Call stored procedure for each item in result set

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)

Call stored procedure for each item in result set

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-20 : 18:48:45

Hi, is it possible to call a stored procedure for each row returned form a query or do I need to user a cursor?

Cheers, XF

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 19:41:26

basically cursor or while loop - but maybe not - what does your procedure do?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-20 : 19:49:18

It updates a set of folders and then does the same for each folder's child folders.

A cursor doesn't work because the DB wants every cursor name to be unique so I'm using a while loop.

But how does one iterate over a table variabe? I'm using an identity column and SELECT- the next row with a higher id than the last.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 22:04:54

same way you would over a "normal" table - here's an example

declare @tab table (id int identity(1,1), name varchar(50))

insert into @tab (name) values ('rob')
insert into @tab (name) values ('rbb')
insert into @tab (name) values ('rrb')

declare @l int
declare @m int

--looping bounds
set @l = 0
select @m = max(id) from @tab 

--loop
while @l <= @m
begin
	--do whatever with each record
	select id, name from @tab where id = @l

	--iterate
	select @l = min(id) from @tab where id > @l
end

It assumes a numeric unique id field, but you get the idea

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-21 : 02:20:14

Also refer this
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-21 : 11:53:57

I did this...

	DECLARE @childFolders table
	(
		id int identity,
		folderID int
	)

	INSERT INTO @childFolders SELECT FolderID FROM tbl_Folders WHERE ParentID = @parentID

	DECLARE @index int, @count int
	SELECT @count = count(*) FROM @childFolders
	SET @index = 1

	WHILE @index <= @count
	BEGIN
		SELECT @parentID = FolderID FROM @childFolders WHERE id = @index
		
		EXEC sp_PropagateFolder @parentID, @rootFolderID, @permissionFolderID

		SET @index = @index + 1
	END

But perhaps the benifit of the the simpler index increment is outweighed by the extra overhead of the identity column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources