SQL Call Stored Procedure for each Row without using a cursor

by

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

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Function vs. Stored Procedure in SQL Server
  3. SQL Server Insert if not exists
  4. SQL Server – How to lock a table until a stored procedure finishes
  5. Stored procedure or function expects parameter which is not supplied
  6. What is a stored procedure?
  7. Search text in stored procedure in SQL Server
  8. SQL Server SELECT into existing table
  9. How do I execute a stored procedure once for each row returned by query?
  10. How to check if a stored procedure exists before creating it
  11. SQL: Select dynamic column name based on variable
  12. SQL : in clause in stored procedure:how to pass values
  13. How to return the output of stored procedure into a variable in sql server
  14. Split values over multiple rows [duplicate]
  15. How do I pass a list as a parameter in a stored procedure?
  16. Must declare the scalar variable
  17. Why execute stored procedures is faster than SQL query from a script?
  18. Custom Date/Time formatting in SQL Server
  19. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  20. Insert Update stored proc on SQL Server
  21. SQL Server: should I use information_schema tables over sys tables?
  22. Inserting rows into a table with one IDENTITY column only [duplicate]
  23. UNION the results of multiple stored procedures
  24. How to pass a temp table as a parameter into a separate stored procedure
  25. Eliminate and reduce overlapping date ranges
  26. Functions vs Stored Procedures
  27. How to execute a stored procedure inside a select query
  28. Dynamic order direction
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Call stored procedure with table-valued parameter from java

Leave a Comment