Exec a Stored proc. for each row
Posted by Anders Vindberg
in Lunarmedia Blog
on the 17 Oct. 2006 (10,954 views).
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;