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

User · Answer

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  lt    MaxI Begin Select  UserID   UserID from  TableUsers Where MyRowCount    i Exec prMyStoredProc  UserID Select    i    i   1   UserID   null End

User · Answer

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  gt   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

User · Answer

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

User · Answer

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

User · Answer

I like the dynamic query way of Dave Rincon as it does not use cursors and is small and easy  Thank you Dave for sharing   But for my needs on Azure SQL and with a  distinct  in the query  i had to modify the code like this   Declare  SQL nvarchar max   -- Set SQL Variable -- Prepare exec command for each distinctive tenantid found in Machines  SELECT  SQL    Select distinct  exec dbo sp S2 Laser to cache                    convert varchar 8  tenantid                       from Dim Machine               where iscurrent   1               FOR XML PATH       --for debugging print the sql  print  SQL   --execute the generated sql script exec sp executesql  SQL    I hope this helps someone

User · Answer

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

User · Answer

You can do it with a dynamic query   declare  cadena varchar max       select  cadena    cadena    exec spAPI     ltrim id        from sysobjects  exec  cadena

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

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures