June 11, 2013 at 11:53 am
Greetings,
The server info is shown below:
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
I have written a stored procedure to be used with a report that must search several columns for keywords. The keywords are stored in a table and I'm declaring a variable to load them and prep the search string as seen below:
declare @Keywords varchar(8000)=N''
select @Keywords=(select '|' + char(34)+'*'+keyword+'*'+char(34) from LocalM5.dbo.TMKeyword where keyword<>'T&M' FOR XML PATH(''))
SELECT @Keywords = STUFF(@Keywords, 1, 1, '')
select * from TMQueue t1 WHERE CONTAINS ((t1.[cust_prob_descr],t1.[MyNote]),@Keywords)
If this is executed in a procedure or stand-alone anywhere after other statements that execute such as populating the table "TMQueue" the query will return no results. If I populate that table and just execute the block from declare to the select statement I get data. So, the first thing I did was print the value for @keywords and build the statement so it executes with no variables like:
select * from TMQueue t1 WHERE CONTAINS ((t1.[cust_prob_descr],t1.[MyNote]),'"*Billable*"|"*Boom*"|"*Bucket*"|"*Cable*"|"*Coax*"|"*DOA*"|"*Fiber*"|"*Fluke*"|"*Ladder*"|"*Lift*"|"*Lightning*"|"*Materials*"|"*Not Onsite*"|"*Rental*"|"*Scissor*"|"*Scope*"|"*Water*"')
Even this does not work within a procedure or if called after any statements that execute before it. I have never seen an issue like this and I don't understand why it executes fine if executed "alone" but not as part of a block.
Cheers
June 11, 2013 at 12:00 pm
I found the cause. If anyone cares the issue is related to truncate vs. delete. I have a table (not a temp or variable) that has constraints, indexes, catalog, etc that I clear out each time the procedure is to run. First I truncate the table then I insert the fresh rows. Use of @@rowcount showed that rows were being inserted yet the final statement using contains still returned nothing. I changed the truncate statement to delete and the problem is gone. This table isn't anything beyond what I described and there are no transactions or anything "special" about the code so I'm at a loss as to why this happened.
Cheers
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply