Hi I have a stored procedure that uses local variables of type TABLE, is it possible to use CONTAINSTABLE on these as it appears to not be working correctly. The CONTAINSTABLE code is at the very bottom of this long piece of code.
CREATE PROCEDURE dbo.SearchJobs
@.STRING varchar(6000),
@.Longitude float,
@.Latitude float,
@.Distance int,
@.Category int
AS
DECLARE @.SEARCHTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50),
SearchColumn text)
DECLARE @.OUTPUTTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50))
DECLARE @.Cat as varchar(200)
IF (NOT @.Category is NULL)
BEGIN
SET @.Cat = 'AND JobCategoryId = ' + @.Category
END
ELSE
BEGIN
SET @.Cat = ''
END
-- This does not calculate exact coordinate distances. It is designed to find all jobs
-- within a certain distance of a reference point. It uses a sqare box for speed as
-- opposed to calculationg a cirecular reference.
IF (NOT @.Longitude is NULL)
BEGIN
EXEC (' INSERT INTO @.SEARCHTABLE SELECT JobId, Type, PromotionCode, Title, JobCategoryId, [Description], ConditionOfEmployment, DollarAmount, DollarType, DateFrom, DateTo, ResumeEmail,
Phone, AreaCode, AddressId, Street, Suburb, PostCode, State, Longitude, Latitude, Positions, EmployerId, Filled, FilledBy, EmployerVisible,
EmployeeVisible, AdditionalSearchString, Street2, Suburb2, PostCode2, State2, Longitude2, Latitude2, AddressId2, Reference, (str(Title) + '' '' + str(Suburb) + '' '' + str(Street) + '' '' + str(PostCode) + CAST([Description] AS varchar))
FROM Job
WHERE (Longitude < (@.Longitude + @.Distance)) AND (Longitude > (@.Longitude - @.Distance)) AND (Latitude < (@.Latitude + @.Distance)) AND (Latitude > (@.Latitude - @.Distance))' + @.Cat)
END
ELSE
BEGIN
EXEC('INSERT INTO @.SEARCHTABLE SELECT * FROM Job WHERE 1 ' + @.Cat)
END
IF(LEN(@.STRING) > 0)
BEGIN
DECLARE @.SearchString varchar(8000)
SET @.SearchString = ''
DECLARE @.INDEX INT
DECLARE @.SLICE nvarchar(4000)
SELECT @.INDEX = 1
DECLARE @.IDCounter int
SET @.IDCounter = 0
IF @.String IS NULL RETURN
WHILE @.INDEX !=0
BEGIN
SELECT @.INDEX = CHARINDEX(' ' ,@.STRING)
IF @.INDEX !=0
BEGIN
SELECT @.SLICE = LEFT(@.STRING,@.INDEX - 1)
END
ELSE
BEGIN
SELECT @.SLICE = @.STRING
--INSERT INTO @.SEARCHTERMS (searchterm) VALUES(@.SLICE)
IF @.SearchString = ''
BEGIN
SET @.SearchString = @.SLICE
END
ELSE
BEGIN
SET @.SearchString = @.SearchString + ' OR ' + @.SLICE
END
SET @.IDCounter = @.IDCounter + 1
SELECT @.STRING = RIGHT(@.STRING,LEN(@.STRING) - @.INDEX)
IF LEN(@.STRING) = 0 BREAK
END
END
END
EXEC sp_fulltext_table @.SEARCHTABLE
SELECT FT_TBL.JobId, FT_TBL.Type, FT_TBL.PromotionCode, FT_TBL.Title, FT_TBL.JobCategoryId, FT_TBL.[Description], FT_TBL.ConditionOfEmployment, FT_TBL.DollarAmount, FT_TBL.DollarType, FT_TBL.DateFrom, FT_TBL.DateTo, FT_TBL.ResumeEmail,
FT_TBL.Phone, FT_TBL.AreaCode, FT_TBL.AddressId, FT_TBL.Street, FT_TBL.Suburb, FT_TBL.PostCode, FT_TBL.State, FT_TBL.Longitude, FT_TBL.Latitude, FT_TBL.Positions, FT_TBL.EmployerId, FT_TBL.Filled, FT_TBL.FilledBy, FT_TBL.EmployerVisible,
FT_TBL.EmployeeVisible, FT_TBL.AdditionalSearchString, FT_TBL.Street2, FT_TBL.Suburb2, FT_TBL.PostCode2, FT_TBL.State2, FT_TBL.Longitude2, FT_TBL.Latitude2, FT_TBL.AddressId2, FT_TBL.Reference,
KEY_TBL.RANK
FROM @.SEARCHTABLE AS FT_TBL INNER JOIN
CONTAINSTABLE (@.SEARCHTABLE,SearchColumn,
@.SearchString
) AS KEY_TBL
ON FT_TBL.JobId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
The deprecated sp_fulltext_table operates on a table that exists in the database, see sp_fulltext_table (Transact-SQL) in Books Online.
Indexes cannot be created explicitly on table variables. See table (Transact-SQL) in Books Online.
No comments:
Post a Comment