July 8, 2003 at 8:49 am
Hi, Hope you can help...
I am trying to write a stored procedure which receives a string (which could have many words listed). It separates each of the words out and then uses them in a select statement where condition using "like" for each of the words.(only one field being used in the where condition)
The problem is there is no limit to the number of words listed.
I have thought about using a while loop to build the SQL but then I get confused because my words are then no longer strings and are part of the SQL. I have been told having a full-text index and using contains is slow - sorry if this is a really thick question.
Nic
July 8, 2003 at 9:13 am
Nic, the thought of parsing through an input text string is something I generally try to avoid.
Have you considered loading the individual words into a global temp table? Than your only argument would be the name of the temp table. Than your query in your sproc could compare with the temp table (subquery).
In order to ensure concurrency, I like to dynamically name the temp table using the GUID function.
To declare the global temp table use something similar to this:
DECLARE @global_temp_table CHAR(12)
SET @global_temp_table = '##dt' + substring(convert(CHAR(255), NEWID()), 1, 8)
Then use dynamic SQL to declare the table:
EXEC('create table ' + @global_temp_table + '
column1 int,
column2 char(25) ) ')
Then load your data into the temp table...
Email me if you need more detail...
July 8, 2003 at 9:20 am
thanks Turpin,
I thought I may be over complicating it. I'll give that a go.
Cheers
Nic
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply