problems with CONTAINSTABLE

[vasah20]

Hello -
I am attempting to use containstable based on a field passed into my stored proc. I know that I can use dynamic SQL to get the containstable to fire, but I heard that this is bad because SQL Server doesn't get to create an execution plan, so in turn I don't want to create dynamic sql.

here's how my code looks:

CREATE PROCEDURE sp_searchFullText
(
@userQuery nvarchar(500)
)
AS
SET NOCOUNT ON
create table #Results
(
pkey int,
ranking int
)

--the following lines spit out errors.
--assume that @userQuery is only one word

INSERT #Results (pkey, ranking)
(SELECT [Key], Rank FROM CONTAINSTABLE products, [name], @newUserQuery))
INSERT #Results (pkey, ranking)
(SELECT [Key], Rank FROM CONTAINSTABLE(products,description, @newUserQuery))

/*I know that this works, but isn't there a way around it?:

DECLARE @newUserQuery NVARCHAR(1024)
set @newUserQuery = 'SELECT [Key], Rank FROM CONTAINSTABLE(aceb2c_product, *, ''' +@newUserQuery+ ''') ORDER BY rank DESC'
INSERT #Results (pkey, ranking) EXEC(@newUserQuery)
*/

DROP TABLE #Results
SET NOCOUNT OFF

thanks in advance
leo

 

[tlbroadbent]

SQL Server always creates a query execution plan. If you use dynamic SQL, SQL Server may not be able to match your execution to a plan in cache and will need to generate a new plan. I think you'll not see much performance degradation with dynamic SQL in this situation.

Therefore, I recommend that you create and execute dynamic SQL statements. Terry

http://members.home.net/tlbroadbent/prog.htm

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes