loop through table executing a stored procedure for each result

[timtom]

Hi,
I have a stored procedure which I want to call from inside another stored procedure. I want to do this a number of times based on rows in a recordset. However, I don't know if this is possible. What I'd like to do is.....

for each ShopId in (select ShopId from Shops where ShopActive = 1)
set @shopid = shopid

exec sp_update_shop @shopid = shopid

loop


I know that is complete rubbish code - but can I do this?
cheers for any help you can give,
Sarah

 

[JohnDTampaBay]

You can do it with a cursor (yuck!). Cursors are notorious resource hogs, but sometimes there's no other way. Set-based operations are much more efficient. Good luck!

DECLARE @ThisShopID int -- Or appropriate datatype

DECLARE ShopCursor CURSOR FOR
  SELECT ShopID FROM Shops WHERE ShopActive = 1

OPEN ShopCursor
FETCH NEXT FROM ShopCursor INTO @ThisShopID 

WHILE @@Fetch_Status = 0
BEGIN
  EXEC sp_update_shop @ShopID = @ThisShopID 

  FETCH NEXT FROM ShopCursor INTO @ThisShopID 
END

CLOSE ShopCursor
DEALLOCATE ShopCursor

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[ESquared]

Why can't your sp_update_shop routine accept a list of shops to handle, and do a simple WHILE loop with a counter variable? It would probably be a ton more efficient.

Look at faq183-5207, "Passing a list of values to a Stored Procedure (Part II)" for some ideas on how to do this efficiently.

-------------------------------------
A sacrifice is harder when no one knows you've made it.