CODEWITHSUNDEEP
sqlt-sqlstored-procedures
František Škandera
František Škandera

Reputation: 187

Run stored procedure for each row of a table

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?

Upvotes: 5

Views: 11405

Answers (3)

PrettyCode
PrettyCode

Reputation: 27

DECLARE @Query varchar(MAX);  
select @Query=STUFF((SELECT 'EXEC dbo.spProcedure @value1=' +QUOTENAME(clm1,'''') 
       + ';' from dbo.my_table
FOR XML PATH('')),1,0,'');
EXEC(@Query);

If you do not want to make any changes to the passing values and pass all the table values in bulk

Upvotes: 0

Sumon Barua
Sumon Barua

Reputation: 57

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

Upvotes: -1

Magnus
Magnus

Reputation: 46919

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

Upvotes: 13

Related Questions