SQL Call Stored Procedure for each Row without usi

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?

Answer 1

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.

Answer 2

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

Answer 3

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 & 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 > @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.

Answer 4

A better solution for this is to

1. Copy/past code of Stored Procedure
2. 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.

Answer 5

DECLARE @SQL varchar(max)=''

-- MyTable has fields fld1 & 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.

Answer 6

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 <> @CustomerIDToHandle
BEGIN  
  SET @LastCustomerId = @CustomerIDToHandle
  -- select the next customer to handle    
  SELECT TOP 1 @CustomerIDToHandle = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @LastCustomerId 
  ORDER BY CustomerID

  IF @CustomerIDToHandle <> @LastCustomerID
  BEGIN
      -- call your sproc
  END

END