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

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?

Solution 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

Solution 2:

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.

Solution 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

Solution 4:

You can do it with a dynamic query.

declare @cadena varchar(max) = ''
select @cadena = @cadena + 'exec spAPI ' + ltrim(id) + ';'
from sysobjects;
exec(@cadena);

Solution 5:

Use a table variable or a temporary table.

As has been mentioned before, a cursor is a last resort. Mostly because it uses lots of resources, issues locks and might be a sign you're just not understanding how to use SQL properly.

Side note: I once came across a solution that used cursors to update rows in a table. After some scrutiny, it turned out the whole thing could be replaced with a single UPDATE command. However, in this case, where a stored procedure should be executed, a single SQL-command won't work.

Create a table variable like this (if you're working with lots of data or are short on memory, use a temporary table instead):

DECLARE @menus AS TABLE (
    id INT IDENTITY(1,1),
    parent NVARCHAR(128),
    child NVARCHAR(128));

The id is important.

Replace parent and child with some good data, e.g. relevant identifiers or the whole set of data to be operated on.

Insert data in the table, e.g.:

INSERT INTO @menus (parent, child) 
  VALUES ('Some name',  'Child name');
...
INSERT INTO @menus (parent,child) 
  VALUES ('Some other name', 'Some other child name');

Declare some variables:

DECLARE @id INT = 1;
DECLARE @parentName NVARCHAR(128);
DECLARE @childName NVARCHAR(128);

And finally, create a while loop over the data in the table:

WHILE @id IS NOT NULL
BEGIN
    SELECT @parentName = parent,
           @childName = child 
        FROM @menus WHERE id = @id;

    EXEC myProcedure @parent=@parentName, @child=@childName;

    SELECT @id = MIN(id) FROM @menus WHERE id > @id;
END

The first select fetches data from the temporary table. The second select updates the @id. MIN returns null if no rows were selected.

An alternative approach is to loop while the table has rows, SELECT TOP 1 and remove the selected row from the temp table:

WHILE EXISTS(SELECT 1 FROM @menuIDs) 
BEGIN
    SELECT TOP 1 @menuID = menuID FROM @menuIDs;

    EXEC myProcedure @menuID=@menuID;

    DELETE FROM @menuIDs WHERE menuID = @menuID;
END;