SQL Call Stored Procedure for each Row without using a cursor

execute stored procedure foreach row in a table sql server
execute stored procedure in loop sql server
mysql call stored procedure for each row
sql cursor
sql run stored procedure in loop
for each loop in sql server stored procedure
execute stored procedure from table
calling a stored procedure in a loop

How can one call a stored procedure for each row in a table, where the columns of a row are input parameters to the sp 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 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. Execute a stored procedure for each row without using a cursor in T-SQL. September 9, 2013 September 12, 2013. If you want to execute a stored procedure for each row in T-SQL 2012, where the Id column is not a consecutive number, you van use the following T-SQL code.

You could do something like this: order your table by e.g. CustomerID (using the AdventureWorks Sales.Customer sample table), and iterate over those customers using a WHILE loop:

-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0

-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT

-- select the next customer to handle    
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerID
ORDER BY CustomerID

-- as long as we have customers......    
WHILE @CustomerIDToHandle IS NOT NULL
BEGIN
    -- call your sproc

    -- set the last customer handled to the one we just handled
    SET @LastCustomerID = @CustomerIDToHandle
    SET @CustomerIDToHandle = NULL

    -- select the next customer to handle    
    SELECT TOP 1 @CustomerIDToHandle = CustomerID
    FROM Sales.Customer
    WHERE CustomerID > @LastCustomerID
    ORDER BY CustomerID
END

That should work with any table as long as you can define some kind of an ORDER BY on some column.

Execute a stored procedure for each row without using a cursor in T , If you want to execute a stored procedure for each row in T-SQL 2012, where the Id column is not a consecutive number, you van use the  Main Stored Procedure: This will be called once per each row of some table. -- All this proc does is, prints out the list of parameters that are passed to it. CREATE PROCEDURE dbo.MyMainStoredProc (

DECLARE @SQL varchar(max)=''

-- MyTable has fields fld1 & fld2

Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ',' 
                   + convert(varchar(10),fld2) + ';'
From MyTable

EXEC (@SQL)

Ok, so I would never put such code into production, but it does satisfy your requirements.

Jon Galloway, [T-SQL] Call a stored procedure once for each row in a query or table Good One, Always wanted to use something else apart from Cursors. myStoredProc' is not a table its a stored procedure weird, any ideas? thanks! through - SQL Call Stored Procedure for each Row without using a cursor sybase execute stored procedure for each row (11) How can one call a stored procedure for each row in a table, where the columns of a row are input parameters to the sp without using a Cursor?

Marc's answer is good (I'd comment on it if I could work out how to!) Just thought I'd point out that it may be better to change the loop so the SELECT only exists once (in a real case where I needed to do this, the SELECT was quite complex, and writing it twice was a risky maintenance issue).

-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1

-- as long as we have customers......    
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN  
  SET @LastCustomerId = @CustomerIDToHandle
  -- select the next customer to handle    
  SELECT TOP 1 @CustomerIDToHandle = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @LastCustomerId 
  ORDER BY CustomerID

  IF @CustomerIDToHandle <> @LastCustomerID
  BEGIN
      -- call your sproc
  END

END

How can I run a proc against all records in a temp table?, The cursor won't take a meaningful amount of time as long as you Here's the code that calls the procedure for each row in the temp table: I would like to execute a stored procedure over each row in a set without using a cursor with something like this: SELECT EXEC dbo.Sproc @Param1 = Table1.id FROM Table1 I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I'd like to use a stored procedure if possible (company standards)

If you can turn the stored procedure into a function that returns a table, then you can use cross-apply.

For example, say you have a table of customers, and you want to compute the sum of their orders, you would create a function that took a CustomerID and returned the sum.

And you could do this:

SELECT CustomerID, CustomerSum.Total

FROM Customers
CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum

Where the function would look like:

CREATE FUNCTION ComputeCustomerTotal
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT SUM(CustomerOrder.Amount) AS Total FROM CustomerOrder WHERE CustomerID = @CustomerID
)

Obviously, the example above could be done without a user defined function in a single query.

The drawback is that functions are very limited - many of the features of a stored procedure are not available in a user-defined function, and converting a stored procedure to a function does not always work.

SQL Server Loop through Table Rows without Cursor, But sometimes there is a need to loop through the data one row at a time, This allows you to use cursors on nested stored procedures. batch execution on this tip: Executing a T-SQL batch multiple times using GO. number on the sequence and we are going to increment its value at each iteration. Here Mudassar Ahmed Khan has provided a tutorial with simple example that explains how to use Cursor in SQL Server Stored Procedures. Cursor is supported in all SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012 and 2014. TAGs: SQL Server, Stored Procedures

T-SQL execute procedure for each row of a master table - b.eye, From time to time it's needed to loop over the rows of a master table – e.g. within your organisation are stored within different databases. DECLARE @​oneid int -- or the appropriate type DECLARE the_cursor CURSOR  Loop through all the rows of a temp table and call a stored procedure for each row. I have declared a temp table to hold all the required values as follows: Now i need to loop through the rows in the @temp table and and for each row call a sp that takes all the parameters of @temp table as input.

https://social.msdn.microsoft.com/Forums/sqlserver, You use cursor variables to pass query sets between PL/SQL stored subprograms and Because there is no type checking with a than calling a procedure -- for each row. This article describes various methods that you can use to simulate a cursor-like FETCH-NEXT logic in a stored procedure, trigger, or Transact-SQL batch. There are three methods you can use to iterate through a result set by using Transact-SQL statements. One method is the use of temp tables.

6 Performing SQL Operations from PL/SQL, You might use sequence numbers to give each row a unique identifier, and refer MINUS returns all distinct rows selected by the first query but not by the second​. Oracle closes the SQL cursor automatically after executing its associated SQL You can also use a standalone stored procedure to open the cursor variable. and in 2005 you can use Row_Number() zmohamed - Tuesday, May 12, 2009 9:17:57 AM; definitely works, but i'd argue a cursor is a much better solution since not only are you going to execute a stored proc for each record, but you are also issuing a select statement for each record . peter - Tuesday, May 12, 2009 10:24:05 PM

Comments
  • So, for example, you have a Customer table with a customerId column, and you want to call the SP once for each row in the table, passing in the corresponding customerId as a parameter?
  • Could you elaborate on why you can't use a cursor?
  • @Gary: Maybe I just want to pass the Customer Name, not necessarily the ID. But you are right.
  • @Andomar: Purely scientific :-)
  • This issue bugs me greatly too.
  • as with the accepted answer USE WITH CATION: Depending on your table and index structure it can be very poorly performing ( O(n^2) ) since you have to order and search your table every time you enumerate.
  • This doesn't seem to work (break never exits loop for me - the work is done but the query spins in the loop). Initializing the id and checking for null in the while condition exits the loop.
  • @@ROWCOUNT can only be read once. Even IF/PRINT statements will set it to 0. The test for @@ROWCOUNT must be done 'immediately' after the select. I would recheck your code/environment. technet.microsoft.com/en-us/library/ms187316.aspx
  • While loops are not better than cursors, be careful, they can be even worse: techrepublic.com/blog/the-enterprise-cloud/…
  • @Brennan Pope Use the LOCAL option for a CURSOR and it will be destroyed upon failure. Use LOCAL FAST_FORWARD and there are almost zero reasons not to use CURSORs for these kind of loops. It would definitely outperform this WHILE loop.
  • @Mitch: yes, true - a bit less overhead. But still - it's not really in the set-based mentality of SQL
  • Is a set based implementation even possible?