SQL Call Stored Procedure for each Row without using a cursor

Question

How can one call a stored procedure for each row in a table  where the columns of a row are input parameters to the sp without using a Cursor

User · Answer

You could do something like this  order your table by e g  CustomerID  using the AdventureWorks Sales Customer sample table   and iterate over those customers using a WHILE loop   -- define the last customer ID handled DECLARE  LastCustomerID INT SET  LastCustomerID   0  -- define the customer ID to be handled now DECLARE  CustomerIDToHandle INT  -- select the next customer to handle     SELECT TOP 1  CustomerIDToHandle   CustomerID FROM Sales Customer WHERE CustomerID  gt   LastCustomerID ORDER BY CustomerID  -- as long as we have customers           WHILE  CustomerIDToHandle IS NOT NULL BEGIN     -- call your sproc      -- set the last customer handled to the one we just handled     SET  LastCustomerID    CustomerIDToHandle     SET  CustomerIDToHandle   NULL      -- select the next customer to handle         SELECT TOP 1  CustomerIDToHandle   CustomerID     FROM Sales Customer     WHERE CustomerID  gt   LastCustomerID     ORDER BY CustomerID END   That should work with any table as long as you can define some kind of an ORDER BY on some column

User · Answer

For SQL Server 2005 onwards  you can do this with CROSS APPLY and a table-valued function    Using CROSS APPLY in SQL Server 2005   Just for clarity  I m referring to those cases where the stored procedure can be converted into a table valued function

User · Answer

This is a variation on the answers already provided  but should be better performing because it doesn t require ORDER BY  COUNT or MIN MAX  The only disadvantage with this approach is that you have to create a temp table to hold all the Ids  the assumption is that you have gaps in your list of CustomerIDs    That said  I agree with  Mark Powell though that  generally speaking  a set based approach should still be better   DECLARE  tmp table  Id INT IDENTITY 1 1  PRIMARY KEY NOT NULL  CustomerID INT NOT NULL  DECLARE  CustomerId INT  DECLARE  Id INT   0  INSERT INTO  tmp SELECT CustomerId FROM Sales Customer  WHILE  1 1  BEGIN     SELECT  CustomerId   CustomerId   Id   Id     FROM  tmp     WHERE Id    Id   1      IF   rowcount   0 BREAK       -- call your sproc     EXEC dbo YOURSPROC  CustomerId  END

User · Answer

Generally speaking I always look for a set based approach  sometimes at the expense of changing the schema      However  this snippet does have its place    -- Declare  amp  init  2008 syntax  DECLARE  CustomerID INT   0  -- Iterate over all customers WHILE  1   1   BEGIN      -- Get next customerId   SELECT TOP 1  CustomerID   CustomerID   FROM Sales Customer   WHERE CustomerID  gt   CustomerId    ORDER BY CustomerID    -- Exit loop if no more customers   IF   ROWCOUNT   0 BREAK     -- call your sproc   EXEC dbo YOURSPROC  CustomerId  END

User · Answer

If you don t what to use a cursor I think you ll have to do it externally  get the table  and then run for each statement and each time call the sp  it Is the same as using a cursor  but only outside SQL  Why won t you use a cursor

User · Answer

DELIMITER     CREATE PROCEDURE setFakeUsers  OUT output VARCHAR 100   BEGIN      -- define the last customer ID handled     DECLARE LastGameID INT      DECLARE CurrentGameID INT      DECLARE userID INT       SET  LastGameID   0        -- define the customer ID to be handled now      SET  userID   0       -- select the next game to handle         SELECT  CurrentGameID   id     FROM online games     WHERE id  gt  LastGameID     ORDER BY id LIMIT 0 1       -- as long as we have customers               WHILE   CurrentGameID IS NOT NULL       DO         -- call your sproc          -- set the last customer handled to the one we just handled         SET  LastGameID    CurrentGameID          SET  CurrentGameID   NULL           -- select the random bot         SELECT  userID   userID         FROM users         WHERE FIND IN SET  bot  baseInfo          ORDER BY RAND   LIMIT 0 1           -- update the game         UPDATE online games SET userID    userID WHERE id    CurrentGameID           -- select the next game to handle             SELECT  CurrentGameID   id          FROM online games          WHERE id  gt  LastGameID          ORDER BY id LIMIT 0 1      END WHILE      SET output    done   END     CALL setFakeUsers  status   SELECT  status

User · Answer

I like to do something similar to this  though it is still very similar to using a cursor    code   -- Table variable to hold list of things that need looping DECLARE  holdStuff TABLE        id INT IDENTITY 1 1         isIterated BIT DEFAULT 0        someInt INT       someBool BIT       otherStuff VARCHAR 200     -- Populate your  holdStuff with    stuff INSERT INTO  holdStuff        someInt       someBool       otherStuff   SELECT       1   -- someInt - int     1   -- someBool - bit      I like turtles   -- otherStuff - varchar 200  UNION ALL SELECT       42   -- someInt - int     0   -- someBool - bit      something profound   -- otherStuff - varchar 200   -- Loop tracking variables DECLARE  tableCount INT SET      tableCount    SELECT COUNT 1  FROM   holdStuff    DECLARE  loopCount INT SET      loopCount   1  -- While loop variables DECLARE  id INT DECLARE  someInt INT DECLARE  someBool BIT DECLARE  otherStuff VARCHAR 200   -- Loop through item in  holdStuff WHILE   loopCount  lt    tableCount      BEGIN          -- Increment the loopCount variable         SET  loopCount    loopCount   1          -- Grab the top unprocessed record         SELECT  TOP 1               id   id                someInt   someInt                someBool   someBool                otherStuff   otherStuff         FROM     holdStuff         WHERE   isIterated   0          -- Update the grabbed record to be iterated         UPDATE   holdAccounts         SET     isIterated   1         WHERE   id    id          -- Execute your stored procedure         EXEC someRandomSp  someInt   someBool   otherStuff      END     code   Note that you don t need the identity or the isIterated column on your temp variable table  i just prefer to do it this way so i don t have to delete the top record from the collection as i iterate through the loop

User · Answer

A better solution for this is to         Copy past code of Stored Procedure   Join that code with the table for which you want to run it again  for each row       This was you get a clean table-formatted output  While if you run SP for every row  you get a separate query result for each iteration which is ugly

User · Answer

DECLARE  SQL varchar max      -- MyTable has fields fld1  amp  fld2  Select  SQL    SQL    exec myproc     convert varchar 10  fld1                              convert varchar 10  fld2        From MyTable  EXEC   SQL    Ok  so I would never put such code into production  but it does satisfy your requirements

User · Answer

I d use the accepted answer  but another possibility is to use a table variable to hold a numbered set of values  in this case just the ID field of a table  and loop through those by Row Number with a JOIN to the table to retrieve whatever you need for the action within the loop   DECLARE  RowCnt int  SET  RowCnt   0 -- Loop Counter  -- Use a table variable to hold numbered rows containg MyTable s ID values DECLARE  tblLoop TABLE  RowNum int IDENTITY  1  1  Primary key NOT NULL       ID INT   INSERT INTO  tblLoop  ID   SELECT ID FROM MyTable    -- Vars to use within the loop   DECLARE  Code NVarChar 10   DECLARE  Name NVarChar 100    WHILE  RowCnt  lt   SELECT COUNT RowNum  FROM  tblLoop  BEGIN     SET  RowCnt    RowCnt   1     -- Do what you want here with the data stored in tblLoop for the given RowNum     SELECT  Code Code   Name LongName       FROM MyTable INNER JOIN  tblLoop tL on MyTable ID tL ID       WHERE tl RowNum  RowCnt     PRINT Convert NVarChar 10   RowCnt         Code        Name END

User · Answer

I had a situation where  I needed to perform a series of operations on a result set  table   The operations are all set operations  so its not an issue  but    I needed to do this in multiple places  So putting the relevant pieces in a table type  then populating a table variable w  each result set allows me to call the sp and repeat the operations each time i need to   While this does not address the exact question he asks  it does address how to perform an operation on all rows of a table without using a cursor   Johannes offers no insight into his motivation   so this may or may not help him  my research led me to this well written article which served as a basis for my solution https   codingsight com passing-data-table-as-parameter-to-stored-procedures  Here is the setup     drop type if exists cpRootMapType  go   create type cpRootMapType as Table      RootId1 int        RootId2 int    go  drop procedure if exists spMapRoot2toRoot1 go  create procedure spMapRoot2toRoot1    map cpRootMapType Readonly   as  update linkTable set root   root1   from linktable  lt  join  map m on lt root   root2  update comments set root   root1  from comments c  join  map m on c root   root2  --  ever growing list of places this map would need to be applied     --  now consolidated into one place   here is the implementation     populate  matches  declare  map cpRootMapType  insert  map select rootid1  rootid2 from  matches exec spMapRoot2toRoot1  map

User · Answer

I had some production code that could only handle 20 employees at a time  below is the framework for the code   I just copied the production code and removed stuff below   ALTER procedure GetEmployees      ClientId varchar 50  as begin     declare  EEList table  employeeId varchar 50        declare  EE20 table  employeeId varchar 50         insert into  EEList select employeeId from Employee where  ClientId    ClientId        -- Do 20 at a time     while  select count    from  EEList   gt  0     BEGIN       insert into  EE20 select top 20 employeeId from  EEList         -- Call sp here        delete  EEList where employeeId in  select employeeId from  EE20        delete  EE20      END     RETURN end

User · Answer

I usually do it this way when it s a quite a few rows    Select all sproc parameters in a dataset with SQL Management Studio Right-click -  Copy Paste in to excel Create single-row sql statements with a formula like    EXEC schema mysproc  param    amp  A2  in a new excel column   Where A2 is your excel column containing the parameter  Copy the list of excel statements into a new query in SQL Management Studio and execute  Done     On larger datasets i d use one of the solutions mentioned above though

User · Answer

If you can turn the stored procedure into a function that returns a table  then you can use cross-apply   For example  say you have a table of customers  and you want to compute the sum of their orders  you would create a function that took a CustomerID and returned the sum   And you could do this   SELECT CustomerID  CustomerSum Total  FROM Customers CROSS APPLY ufn ComputeCustomerTotal Customers CustomerID  AS CustomerSum   Where the function would look like   CREATE FUNCTION ComputeCustomerTotal        CustomerID INT   RETURNS TABLE AS RETURN       SELECT SUM CustomerOrder Amount  AS Total FROM CustomerOrder WHERE CustomerID    CustomerID     Obviously  the example above could be done without a user defined function in a single query   The drawback is that functions are very limited - many of the features of a stored procedure are not available in a user-defined function  and converting a stored procedure to a function does not always work

User · Answer

In case the order is important  --declare counter DECLARE      CurrentRowNum BIGINT   0  --Iterate over all rows in  DataTable  WHILE  1   1      BEGIN         --Get next row by number of row         SELECT TOP 1  CurrentRowNum   extendedData RowNum                     --here also you can store another values                     --for following usage                     -- MyVariable   extendedData Value         FROM                          SELECT                          data                            ROW NUMBER   OVER ORDER BY  SELECT 0   RowNum                     FROM  DataTable  data                   extendedData         WHERE extendedData RowNum  gt   CurrentRowNum         ORDER BY extendedData RowNum          --Exit loop if no more rows         IF   ROWCOUNT   0 BREAK           --call your sproc         --EXEC dbo YOURSPROC  MyVariable     END

User · Answer

Marc s answer is good  I d comment on it if I could work out how to   Just thought I d point out that it may be better to change the loop so the SELECT only exists once   in a real case where I needed to do this  the SELECT was quite complex  and writing it twice was a risky maintenance issue    -- define the last customer ID handled DECLARE  LastCustomerID INT SET  LastCustomerID   0 -- define the customer ID to be handled now DECLARE  CustomerIDToHandle INT SET  CustomerIDToHandle   1  -- as long as we have customers           WHILE  LastCustomerID  lt  gt   CustomerIDToHandle BEGIN     SET  LastCustomerId    CustomerIDToHandle   -- select the next customer to handle       SELECT TOP 1  CustomerIDToHandle   CustomerID   FROM Sales Customer   WHERE CustomerID  gt   LastCustomerId    ORDER BY CustomerID    IF  CustomerIDToHandle  lt  gt   LastCustomerID   BEGIN       -- call your sproc   END  END

User · Answer

This is a variation of n3rds solution above  No sorting by using ORDER BY is needed  as MIN   is used   Remember that CustomerID  or whatever other numerical column you use for progress  must have a unique constraint  Furthermore  to make it as fast as possible CustomerID must be indexed on    -- Declare  amp  init DECLARE  CustomerID INT    SELECT MIN CustomerID  FROM Sales Customer   -- First ID DECLARE  Data1 VARCHAR 200   DECLARE  Data2 VARCHAR 200    -- Iterate over all customers WHILE  CustomerID IS NOT NULL BEGIN      -- Get data based on ID   SELECT  Data1   Data1   Data2   Data2     FROM Sales Customer     WHERE  ID     CustomerID      -- call your sproc   EXEC dbo YOURSPROC  Data1   Data2    -- Get next customerId   SELECT  CustomerID   MIN CustomerID      FROM Sales Customer     WHERE CustomerID  gt   CustomerId   END   I use this approach on some varchars I need to look over  by putting them in a temporary table first  to give them an ID

[sql] SQL Call Stored Procedure for each Row without using a cursor

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures

Examples related to cursor