Execute sql using a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Execute sql using a loop

Author

Topic

EA
Starting Member

19 Posts

Posted - 2006-07-12 : 15:16:36

Hi,
Because I'me a newbie on this... and I don't want to make a monstrous-query, please some advice on this:

In pseudo-code:

for objectname in (specified list of objects)
do
some sql code (i.e. create table xyz)
done

With 'objects' I mean a database or table name.

I've searched and found the foreachdb option, but I don't want to execute the sql n ALL db's but only the ones specified.

Any help is appreciated!
Reply With Quote

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-07-12 : 18:10:12

You can use a table variable to store the list of objects and then "walk" through each row using a counter:

set nocount on
go

declare @Process table (ProcessID int identity(1,1), DatabaseName varchar(100))
insert into @Process
	select 'Database Uno'	union
	select 'Database Dos'	union
	select 'Database Tres'

declare @ProcessID int,
	@Cmd varchar(1000)

select	@ProcessID = min(ProcessID)
from	@Process

while @ProcessID is not null
begin

	select	@Cmd = 'sp_helpdb ''' + DatabaseName + ''''
	from	@Process
	where	ProcessID = @ProcessID

	-- do action here
	print(@cmd)

	select	@ProcessID = min(ProcessID)
	from	@Process
	where	ProcessID > @ProcessID
end

Nathan Skerl

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-12 : 19:03:55

You can do this without using table variables and cursors by using a WHERE loop.


DECLARE @lastID int
SET @lastID = 0
WHILE EXISTS (SELECT NULL FROM yourTable WHERE idField > @lastID) BEGIN
    SELECT TOP 1 @lastID = idField, fields.... FROM yourTable 
    WHERE idField > @lastID
    ORDER BY idField ASC
END

This avoids using tempdb

Tim

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-07-12 : 20:00:56

You would only incur I/O to TempDB if the query occupied all of the server's RAM. I seriously doubt that EA's query would come anywhere close to resulting in this.

I figured EA would only be passing a select few object names.

As I understand the question, EA wants to cycle through a "specified list of objects" and perform a dynamic sql action on them (similar to foreachdb).

Nathan Skerl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-12 : 20:06:37

quote:
Originally posted by timmy

You can do this without using table variables and cursors by using a WHERE loop.

DECLARE @lastID int
SET @lastID = 0
WHILE EXISTS (SELECT NULL FROM yourTable WHERE idField > @lastID) BEGIN
SELECT TOP 1 @lastID = idField, fields.... FROM yourTable
WHERE idField > @lastID
ORDER BY idField ASC
END

This avoids using tempdb

Tim

I don't see how this cycles through databases or tables. He/she wants to perform an action in each specified database or on each specified table/database object.

sp_foreachdb would work except that he/she doesn't want to perform the action in each of the databases on the server, but rather a subset. So looping (cursor or WHILE loop) is required.

Tara Kizer
aka tduggan

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-12 : 21:05:06

You're right Tara, but I was just illustrating how to use loops in TSQL without using temp tables/table vars.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources