INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

Help building sp_executesql script

Help building sp_executesql script

(OP)
Hi. I have a table

CODE

create table searchlist (list varchar (100)) 

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

CODE

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:

CODE

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:

CODE

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!

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

CODE

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

Borislav Borissov
VFP9 SP2, SQL Server

RE: Help building sp_executesql script

Play around with this one.

CODE --> T-SQL

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.

RE: Help building sp_executesql script

(OP)
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!

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.

CODE

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 

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!

Resources

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