SQL - Call Stored Procedure for Each Record

SQL Call Stored Procedure for each Row without using a cursor

Generally speaking I always look for a set based approach (sometimes at the expense of changing the schema).

However, this snippet does have its place..

-- Declare & init (2008 syntax)
DECLARE @CustomerID INT = 0

-- Iterate over all customers
WHILE (1 = 1) 
BEGIN  

  -- Get next customerId
  SELECT TOP 1 @CustomerID = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @CustomerId 
  ORDER BY CustomerID

  -- Exit loop if no more customers
  IF @@ROWCOUNT = 0 BREAK;

  -- call your sproc
  EXEC dbo.YOURSPROC @CustomerId

END

SQL - Call Stored Procedure for each record

You need to use a cursor for that.

DECLARE @oneid int -- or the appropriate type

DECLARE the_cursor CURSOR FAST_FORWARD
FOR SELECT spro.Id  
    FROM SomeTable as spro 
        INNER JOIN [Address] addr ON addr.Id = spro.Id  
        INNER JOIN City cty ON cty.CityId = addr.CityId 
    WHERE cty.CityId = @CityId

OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @oneid

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC UpdateComputedFullText @oneid

    FETCH NEXT FROM the_cursor INTO @oneid
END

CLOSE the_cursor
DEALLOCATE the_cursor

How do I execute a stored procedure once for each row returned by query?

use a cursor

ADDENDUM: [MS SQL cursor example]

declare @field1 int
declare @field2 int
declare cur CURSOR LOCAL for
    select field1, field2 from sometable where someotherfield is null

open cur

fetch next from cur into @field1, @field2

while @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    exec uspYourSproc @field1, @field2

    fetch next from cur into @field1, @field2
END

close cur
deallocate cur

in MS SQL, here's an example article

note that cursors are slower than set-based operations, but faster than manual while-loops; more details in this SO question

ADDENDUM 2: if you will be processing more than just a few records, pull them into a temp table first and run the cursor over the temp table; this will prevent SQL from escalating into table-locks and speed up operation

ADDENDUM 3: and of course, if you can inline whatever your stored procedure is doing to each user ID and run the whole thing as a single SQL update statement, that would be optimal

Run stored procedure for each row of a table

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

Execution of Stored Procedure for every row of table using string variable

You could use CURSOR to simulate FOR-EACH with stored procedure call:

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR 
SELECT *FROM  dbo.tesT_User;

DECLARE @name NVARCHAR(50);

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

WHILE @@FETCH_STATUS = 0  
BEGIN  
      EXEC dbo.usp_TestSP @name = @name;

      FETCH NEXT FROM db_cursor INTO @name;
END 

CLOSE db_cursor;
DEALLOCATE db_cursor;

DBFiddle Demo

---

Warning:

If it would be simple function then CROSS/OUTER APPLY is a way to go.

SELECT *
FROM dbo.Test_User tu
OUTER APPLY (SELECT dbo.usp_TestSP(tu.name)) s(a);

DBFiddle Demo2

---

EDIT

i want to execute this without cursor or loop, using the same logic as displayed above

DECLARE @x NVARCHAR(MAX) = 
   (SELECT string_agg(FORMATMESSAGE('EXEC dbo.usp_TestSP @name=''%s'';', name)
   ,CHAR(13)) AS r FROM dbo.test_User);
PRINT @x;
EXEC(@x);

DBFiddle Demo3

---

And finally your code:

DECLARE @Query varchar(MAX);  
select @Query=STUFF((SELECT 'EXEC dbo.usp_TestSP @name=' +QUOTENAME(Name,'''') 
       + ';' from dbo.test_user
FOR XML PATH('')),1,0,'');
EXEC(@Query);

DBFiddle Demo4

What I did:

• using semicolons (;) it's very good practice
• changed datatype to VARCHAR(MAX)
• added EXEC inside SQL
• wrapped @Query with ()

How to exec a stored procedure for each row in a select statement?

Taking the singular INSERT INTO.. SELECT apart:

Temporarily store the SELECT results away

 declare @rc int, @NewSeqVal int;
 SELECT ..
   INTO #tmp -- add this
   FROM ..

Store the rowcount and get that many numbers

 set @rc = @@rowcount;

For which you have to use the code in the SP directly:

 update Number --This is a table that holds for each table the max ID
 set @NewSeqVal = Next = Next + @rc
 where TableNaam= 'sometbl';

Finally, the insert

 INSERT ... 
 SELECT ID = @NewSeqVal + 1 - row_number() over (ORDER BY col1)
       , {all the other columns}
 FROM #tmp;

ORDER by Col1 is arbitrary, choose something sensible, or make it ORDER BY NEWID() if you don't care.

---

Related Topics