May 4, 2009 at 12:59 pm
Hi,
I have searchcriteria values stored in table searchinfo a (200 records).
I want to search from infosource column of table custinfo b.
What is the syntax for searching values like a.searchcriteria in b.infosoure?
One way I was thinking was to use dynamic sql and putting it in a loop to go through all the records in search table.
declare @sqlstatement varchar(1500)
select @sqlstatement =
'SELECT InfoId, InfoSource
FROM Custinfo b
WHERE CreatedDate > ''03/01/09'' AND Infosource LIKE ''%' + convert(varchar(20), a.searchcriteria) + '%'''
from SearchInfo b
exec (@sqlstatement)
Search table is 200 records. Custinfo table is over million record.
Is there any other way or better way to do this?
Thanks,
Nikki
May 4, 2009 at 1:56 pm
Either way you look at it, SQL Server will perform a table scan because of the %value% search. Here's a way to work it w/o dynamic SQL:
declare @table table (rowid int, rowvalue varchar(20))
insert into @table
select 1, 'One for the Money' union all
select 2, 'two for the show' union all
select 3, 'three to get ready' union all
select 4, 'and four to go'
declare @table2 table(searchvalues varchar(10))
insert into @table2
select 'for the'
select *
from @table t1
inner join @table2 t2
on t1.rowvalue like ('%'+t2.searchvalues+'%')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply