December 13, 2002 at 8:47 am
I'd like to write a stored procedure that takes an optional parameter to search on product description in a full-text index.
If the parameter is null, I want all records to be returned. What can I pass to @ProdDesc to return all rows in the following proc?
create proc prc_SelectProds(@ProdDesc varchar(50) = NULL) as
select * from containstable(product, desc, @ProdDesc)
I'm really over-simplifying this as my true query contains a few CONTAINSTABLE joins and about 10 parameters. So, simply writing a case statement to select everything from the table without using the CONTAINSTABLE is kind of out of the question.
Thanks,
Jay
December 16, 2002 at 8:00 am
This was removed by the editor as SPAM
December 16, 2002 at 9:55 am
SQL7 BOL states that variables are not allowed for search conditions. If you have a lot of these then I suggest you build a sql string and use executesql. Try
declare @sql nvarchar(4000)
set @sql = 'select * from containstable(product, desc, '+isnull(@ProdDesc,'''*''')+')'
executesql(@sql)
cannot guarantee performance though.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply