Run stored procedure for each row of a table

Question

I have a table that look something like this

value_1  value_2  value_3
---------------------------
   1        2        3
   4        5        6
   7        8        9
   ...     ...     ...

and I need to run a stored procedure for every row of this table, something like

foreach value_1, value_2, value_3 from my_table
exec spProcedure value_1, value_2, value_3

Is there a way to do this?

Possible duplicate of [SQL - Call Stored Procedure for each record](https://stackoverflow.com/questions/2077948/sql-call-stored-procedure-for-each-record) — Michael Freidgeim, Nov 13 '19 at 12:26

score 13 · Accepted Answer · answered Jun 11 '14 at 07:37

You can use a cursor:

DECLARE @value1 INT,
        @value2 INT,
        @value3 INT 

DECLARE cursorElement CURSOR FOR
            SELECT  value_1, value_2, value_2
            FROM    table


OPEN cursorElement
FETCH NEXT FROM cursorElement INTO @value1, @value2, @value3

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
    exec spProcedure @value1, @value2, @value3

    FETCH NEXT FROM cursorElement INTO @value1, @value2, @value3
END         
CLOSE cursorElement
DEALLOCATE cursorElement

score -1 · Answer 2 · answered Jun 11 '14 at 07:56

Another way:

DECLARE @MemoryTable table (
    RowID int,
    value_1 int,
    value_2 int,
    value_3 int
)

INSERT INTO @MemoryTable
SELECT ROW_NUMBER() OVER (ORDER BY value_1) AS RowID, * FROM SampleTable


DECLARE @ctr INT
DECLARE @MaxRownum INT

DECLARE @val1 INT
DECLARE @val2 INT
DECLARE @val3 INT


SET @ctr = 1
SET @MaxRownum = (SELECT COUNT(1) FROM @MemoryTable)


WHILE @ctr <= @MaxRownum
BEGIN

    SELECT @val1 = value_1,
           @val2=value_2,
           @val3=value_3
    FROM @MemoryTable
    WHERE RowID = @ctr

    exec spProcedure @val1, @val2, @val3

    SET @ctr = @ctr + 1
END

Run stored procedure for each row of a table

2 Answers2