Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Help building sp_executesql script

Help building sp_executesql script

Help building sp_executesql script

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')

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

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

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?


RE: Help building sp_executesql script

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

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.

RE: Help building sp_executesql script


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

Borislav Borissov
VFP9 SP2, SQL Server

RE: Help building sp_executesql script

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 + '%'' 
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.

RE: Help building sp_executesql script

Thanks bborrisov. That was my issue.

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


RE: Help building sp_executesql script

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.


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 @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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close