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

use a cursor

ADDENDUM: [MS SQL cursor example]

declare @field1 intdeclare @field2 intdeclare cur CURSOR LOCAL for    select field1, field2 from sometable where someotherfield is nullopen curfetch next from cur into @field1, @field2while @@FETCH_STATUS = 0 BEGIN    --execute your sproc on each row    exec uspYourSproc @field1, @field2    fetch next from cur into @field1, @field2ENDclose curdeallocate 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

try to change your method if you need to loop!

within the parent stored procedure, create a #temp table that contains the data that you need to process. Call the child stored procedure, the #temp table will be visible and you can process it, hopefully working with the entire set of data and without a cursor or loop.

this really depends on what this child stored procedure is doing. If you are UPDATE-ing, you can "update from" joining in the #temp table and do all the work in one statement without a loop. The same can be done for INSERT and DELETEs. If you need to do multiple updates with IFs you can convert those to multiple UPDATE FROM with the #temp table and use CASE statements or WHERE conditions.

When working in a database try to lose the mindset of looping, it is a real performance drain, will cause locking/blocking and slow down the processing. If you loop everywhere, your system will not scale very well, and will be very hard to speed up when users start complaining about slow refreshes.

Post the content of this procedure you want call in a loop, and I'll bet 9 out of 10 times, you could write it to work on a set of rows.

Something like this substitutions will be needed for your tables and field names.

Declare @TableUsers Table (User_ID, MyRowCount Int Identity(1,1)Declare @i Int, @MaxI Int, @UserID nVarchar(50)Insert into @TableUserSelect User_IDFrom Users Where (My Criteria)Select @MaxI = @@RowCount, @i = 1While @i <= @MaxIBeginSelect @UserID = UserID from @TableUsers Where MyRowCount = @iExec prMyStoredProc @UserIDSelect @i = @i + 1, @UserID = nullEnd