Passing parameter to freetexttable query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Passing parameter to freetexttable query

Author

Topic

Matt2k9
Starting Member

10 Posts

Posted - 2008-09-26 : 07:12:01

Hi, I'm trying to pass a parameter to this full-text search query but I'm making a syntax error when I try to pass it in:

select *
from product
inner join freetexttable(product, [productname], 'isabout(" ' + @searchterm + ' " ') as KEY_TBL
on product.productid = KEY_TBL.[KEY]
order by rank desc

The above works perfectly if I hardcode something in place of + @searchterm +, however if I try to use a parameter as above, I get the error "incorrect syntax near +", so management studio clearly does not like how I am trying to pass in the parameter.

Any ideas would be much appreciated!!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 07:15:05

i think you need to use dynamic sql here
something like

DECLARE @Sql varchar(8000)

SET @Sql='select *
from product
inner join freetexttable(product, [productname], 'isabout(" ' + @searchterm + ' " ') as KEY_TBL
on product.productid = KEY_TBL.[KEY]
order by rank desc'

EXEC(@Sql)

Matt2k9
Starting Member

10 Posts

Posted - 2008-09-26 : 07:24:00

This works, I used sp_executesql, would this not make the query vunerable to sql injection attack though??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 07:25:30

quote:
Originally posted by Matt2k9

This works, I used sp_executesql, would this not make the query vunerable to sql injection attack though??

yup it will. Provided you've not included enough validations at your front end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources