Execute a Stored Procedure with OUTPUT For Each Row in SELECT Statement

Question

Is it possible to execute a stored procedure for each row in a SELECT? This only executes the first row, looking for something to execute for all rows:

    Declare 
        @Loop bit = 1, @ID int, @Exists bit, @ReturnValue bit = 0

    WHILE (@Loop) = 1
        BEGIN

            SELECT @ID = ID FROM Table --Multiple Rows Returned

            EXEC [dbo].[StoredProc1] --Exec SP for Each Row
                @InputID = @ID
                ,@Exists = @Exists OUTPUT

            IF @Exists = 1 
                 BEGIN
                     SET @Loop = 0
                     SET @ReturnValue = 1
                 END

        END

    SELECT @ReturnValue [ReturnValue]

pete · Accepted Answer · 2012-02-15 02:44:57Z

6

Use a cursor:

DECLARE @exists     bit
DECLARE db_cursor   CURSOR FOR  
SELECT  ID 
FROM    Table

OPEN    db_cursor   
FETCH NEXT FROM db_cursor INTO @ID   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       EXEC [dbo].[StoredProc1] --Exec SP for Each Row
            @ID
            , @exists OUTPUT  

       FETCH NEXT FROM db_cursor INTO @id   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

answered Feb 15, 2012 at 2:44

pete

25.2k4 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aaron Bertrand Over a year ago

Strongly recommend adding LOCAL STATIC READ_ONLY FORWARD_ONLY to the cursor declaration.

Gabe Over a year ago

@AaronBertrand: If you're going to recommend a bunch of voodoo like that, you should specify why -- otherwise it just encourages cargo cult programming. I certainly have no idea why you'd bother to specify READ_ONLY FORWARD_ONLY on a STATIC cursor, and using STATIC can have pretty serious repercussions if you use it wrong. Is there any need for LOCAL on a cursor that gets deallocated when it's closed, like in the given code?

Aaron Bertrand Over a year ago

@Gabe I've since written this post, and it is not a "bunch of voodoo": sqlperformance.com/2012/09/t-sql-queries/cursor-options However, based on code I still see every day, I'm not too concerned that my passing comment is going to flip the industry upside down.

Gabe Over a year ago

@AaronBertrand: I'm afraid your post wasn't very enlightening. It appears to recommend FAST_FORWARD, and doesn't show a meaningful effect from adding READ_ONLY FORWARD_ONLY.

Aaron Bertrand Over a year ago

@Gabe did you read the whole post, e.g. the part where I mention that I used to recommend LOCAL STATIC READ_ONLY FORWARD_ONLY? Sorry I didn't go back in time and change every comment I've made about this all over the web.

|

Collectives™ on Stack Overflow

Execute a Stored Procedure with OUTPUT For Each Row in SELECT Statement

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related