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

by

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

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 Call Stored Procedure for each Row without using a cursor
  9. SQL Server SELECT into existing table
  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. SQL Server silently truncates varchar’s in stored procedures
  15. Split values over multiple rows [duplicate]
  16. How do I pass a list as a parameter in a stored procedure?
  17. Must declare the scalar variable
  18. Why execute stored procedures is faster than SQL query from a script?
  19. Stored procedure that exports data into csv files only exports to one file
  20. Custom Date/Time formatting in SQL Server
  21. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  22. Insert Update stored proc on SQL Server
  23. SQL Server: should I use information_schema tables over sys tables?
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. UNION the results of multiple stored procedures
  26. How to pass a temp table as a parameter into a separate stored procedure
  27. Eliminate and reduce overlapping date ranges
  28. How to execute a stored procedure inside a select query
  29. Dynamic order direction
  30. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?

Leave a Comment