Exec a Stored proc. for each row

Have you ever needed to execute a stored procedure for each row in a table? In my latest Community Server mashup project we wanted to delete all posts in a particular weblog. In my perfect world it would look like something like this:

Exec sp_cleanup @postID = SELECT PostID FROM Posts.

A real-world solution using a cursor looks like this (It deletes all posts in a blog):

DECLARE @PostID int;
DECLARE @SectionID int;
DECLARE @SettingsID int;
DECLARE @Deletedby int;

SET @SectionID = 27;
SET @SettingsID = 1000;
SET @Deletedby = 2100;

DECLARE valuesCursor CURSOR FOR
SELECT PostID FROM dbo.cs_posts WHERE SectionID = @SectionID;

OPEN valuesCursor;
FETCH NEXT FROM valuesCursor
INTO @postID;
WHILE @@FETCH_STATUS = 0 BEGIN

EXEC [dbo].cs_Post_Delete
@SectionID = @SectionID,
@SettingsID = @SettingsID,
@PostID = @PostID,
@DeletedBy = @DeletedBy

FETCH NEXT FROM valuesCursor INTO @postID;
END

CLOSE valuesCursor;
DEALLOCATE valuesCursor;

↓ 2 comments
↓ Add Comment

Comments (2)

Dave Burke said October 17, 2006 01:32 PM
This is a great tip to blog on, Anders.  I blogged on it a while back myself, but forgot how to do it since then.  Thanks for posting it!

Anders Vindberg said October 18, 2006 06:42 AM
Hi Dave, nice to see you here again. I always forget how these things are done, particularly in these days when work with T-Sql.is sparse because of build-in business logic in CS :)