How Can I Call a Stored Procedure for Each Row in a Table Without Cursors?

Calling a Stored Procedure for Each Row Without a Cursor

If you need to invoke a stored procedure for every row in a table, but wish to avoid using a cursor, you can utilize a set-based approach. Here's a code snippet to guide you:

-- 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

This code iterates over a table, retrieves the next customer's ID, and invokes the stored procedure YOURSPROC with the ID as the parameter. It continues this loop until there are no more customers to process.

The above is the detailed content of How Can I Call a Stored Procedure for Each Row in a Table Without Cursors?. For more information, please follow other related articles on the PHP Chinese website!