Help building sp_executesql script 2

[bmacbmac]

Hi. I have a table

create table searchlist (list varchar (100))

Which contains about 100 items I need to search in 75 databases. I can run something like this:

select t1.list from MyDB1..files t1
join searchlist t2
on t1.list like '%' + t2.list + '%'

And I receive results just fine.



Instead of running 75 different queries, I have created a table and inserted all 75 database names in it:

create table dblist (counter int identity(1,1), list varchar(100), processed varchar (1))
insert into dblist (list) values ('MyDB1')
insert into dblist (list) values ('MyDB2')
insert into dblist (list) values ('MyDB3')
insert into dblist (list) values ('MyDB4')
etc.

I am trying a 'while' loop to search each file in the 'searchlist' by joining it with each of my 75 databases:

while (select count(counter) from dblist where processed is null) > 0
begin

declare @mindb varchar(100)
declare @dynamicsql nvarchar(2000)

set @mindb = (select min(list) from dblist where processed is null)

set @dynamicsql = 'insert into results 
select t1.list from ' + @mindb + '..files t1 ' + 
'join searchlist  
on t1.list like ' + '%' + '''' + ' + searchlist.list + ' + '''' + '%' + ''''

print @dynamicsql
sp_executesql @dynamicsql

update sharelist set processed = 'x'
where list = @mindb
end

I am getting something wrong in the @dynamicSQL syntax. I don't think I am joining the tables correctly as far as the syntax goes.

Can anyone tell me what I'm missing here?

Thanks!

 

[Olaf Doschke]

[tt]on t1.list like [highlight #EF2929]' + [/highlight] '%' + '''' [highlight #EF2929]+ ' [/highlight] + searchlist.list [highlight #EF2929]+ ' [/highlight] + '''' + '%' + ''''[/tt]

Any of these things don't work at all, a single ' is not even an empty string. Also, while you're inside a string, there is no need to end it to start concatenate it with '''', simply have '' within the already opened string part and continue, eg to get %'test'% within a string, you double these quotes as %''test''% and so the string notation of $'test'% is '%''test''%', not '%'+ '''' + 'test' + ''''+ '%', isn't it simpler to not cut the strings into pieces?

Besides that error in your syntax, you missed things like sp_MSforeachdb and sp_MSforeachtable system stored procedures, very helpful to do something with every table of every database.

Bye, Olaf.

 

[bborissov]

on t1.list like ''%' + searchlist.list + '%'''

Borislav Borissov
VFP9 SP2, SQL Server

 

[philhege]

Play around with this one.

declare @dynamicsql varchar(max)
set @dynamicsql = 'INSERT INTO Results SELECT'

select @dynamicsql = @dynamicsql + ' 
 t1.list from ' + dblist.list + '..files t1 
join searchlist  
on t1.list like ''%' + searchlist.list + '%'' 
UNION SELECT'
FROM dblist
JOIN searchlist on 1 = 1

SET @dynamicsql = @dynamicsql + ' CUT'
SET @dynamicsql = REPLACE(@dynamicsql,'UNION SELECT CUT','')

print @dynamicsql
--sp_executesql @dynamicsql

-----------
With business clients like mine, you'd be better off herding cats.

 

[bmacbmac]

Thanks bborrisov. That was my issue.

philhege - I see what you did there. I'll play around with it and see how it goes.

Thanks!

 

[DaveInIowa]

Just in case joining with LIKE is a performance issue, here's another option. It uses a hairy CASE statement but I think TSQL can handle at least 100 WHEN/THEN cases. I don't know if it would perform any better than the JOINs or not.

DECLARE @SearchCriteria VARCHAR(MAX) = ' WHERE CASE'
SELECT @SearchCriteria = @SearchCriteria + ' WHEN list LIKE ''%' + list + '%'' THEN 1' FROM searchlist
SET @SearchCriteria += ' ELSE 0 END = 1'

DECLARE @DbList TABLE (RowNumber INT IDENTITY(1,1), DbName VARCHAR(100))
INSERT INTO @DbList SELECT list from dblist WHERE processed IS NULL
DECLARE @RowCount INT = @@ROWCOUNT

DECLARE @RowNumber INT = 1
WHILE (@RowNumber <= @RowCount) BEGIN
	DECLARE @DbName VARCHAR(100) = (SELECT DbName FROM @DbList WHERE RowNumber = @RowNumber)

	DECLARE @DynamicSql VARCHAR(MAX) = 'INSERT INTO Results SELECT list FROM ' + @DbName + '..files' + @SearchCriteria

	PRINT @DynamicSql

	SET @RowNumber += 1
END