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
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()
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
Call a stored procedure for each row returned by a query in MySQL
Concepts such as “loops” (for-each, while, etc) and “branching” (if-else, call, etc) are procedural and do not exist in declarative languages like SQL. Usually one can express one's desired result in a declarative way, which would be the correct way to solve this problem.
For example, if the testProc
procedure that is to be called uses the given id
as a lookup key into another table, then you could (and should) instead simply JOIN
your tables together—for example:
SELECT ...
FROM objects JOIN other USING (id)
WHERE ...
Only in the extremely rare situations where your problem cannot be expressed declaratively should you then resort to solving it procedurally instead. Stored procedures are the only way to execute procedural code in MySQL. So you either need to modify your existing sproc so that it performs its current logic within a loop, or else create a new sproc that calls your existing one from within a loop:
CREATE PROCEDURE foo() BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE _id BIGINT UNSIGNED;
DECLARE cur CURSOR FOR SELECT id FROM objects WHERE ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
OPEN cur;
testLoop: LOOP
FETCH cur INTO _id;
IF done THEN
LEAVE testLoop;
END IF;
CALL testProc(_id);
END LOOP testLoop;
CLOSE cur;
END
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
Related Topics
How to Make SQL Query Result Show With 2 Decimals
Query: Fetch 3 Records Which Has Higher Value
How to Get Last 7 Days Data from Current Datetime to Last 7 Days in SQL Server
Wamp Server Error [Local Server - 2 of 3 Services Running]
Checking a Column If It Contains a Row Value
How to Convert Bigint (Unix Timestamp) to Datetime in SQL Server
How to Delete Multiple Rows in Diferent Tables
Using SQL to Determine Word Count Stats of a Text Field
What Would Be a SQL Query to Remove \N\R from the Text
Mysql Count() to Return 0 If No Records Found
How to Select All the Columns of a Table Except One Column
How to Get a List of Column Names on Sqlite3 Database
How to Add a Space Between Two Text in SQL Code
How to Pass Multiple Values to Single Parameter in Stored Procedure
How to Split One Column into Two Columns in SQL Server
Mysql Truncated Incorrect Double Value
Oracle Pl/Sql String Compare Issue
How to Get the Unmatched Records from Two Tables Using Joins