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

Question

I have a stored procedure that alters user data in a certain way. I pass it user_id and it does it's thing. I want to run a query on a table and then for each user_id I find run the stored procedure once on that user_id

How would I write query for this?

SQL SERVER

Answer 1

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

Answer 2

It's best if you can avoid this. Most databases are optimized for set based actions, and looping using a cursor will be relatively slow.

That said, here is a good example for how to do this using SQL Server

Answer 3

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 @TableUser
Select User_ID
From Users 
Where (My Criteria)
Select @MaxI = @@RowCount, @i = 1

While @i <= @MaxI
Begin
Select @UserID = UserID from @TableUsers Where MyRowCount = @i
Exec prMyStoredProc @UserID
Select

 @i = @i + 1, @UserID = null
End

Answer 4

If you can give more details, this task may be achievable by update query with joins.

Answer 5

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 updating, 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 data base try to loos 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 user 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.

Answer 6

Wouldn't it be nice to have something like SELECT someProcedure(AuthorID) FROM authors ...ah, just dreaming..!

Answer 7

Can this not be done with a user-defined function to replicate whatever your stored procedure is doing?

SELECT udfMyFunction(user_id), someOtherField, etc FROM MyTable WHERE WhateverCondition

where udfMyFunction is a function you make that takes in the user ID and does whatever you need to do with it.

See http://www.sqlteam.com/article/user-defined-functions for a bit more background

I agree that cursors really ought to be avoided where possible. And it usually is possible!

(of course, my answer presupposes that you're only interested in getting the output from the SP and that you're not changing the actual data. I find "alters user data in a certain way" a little ambiguous from the original question, so thought I'd offer this as a possible solution. Utterly depends on what you're doing!)