SQL Call Stored Procedure for each Row

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

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 ?

Answer 2

For SQL Server 2005 onwards, you can do this with CROSS APPLY and a table-valued function.

• Using CROSS APPLY in SQL Server 2005

Answer 3

You could slap it into a udf- but i don't think you'd want to do that in reality.

Answer 4

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

Marc

Answer 5

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.

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 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 <> @LastCustoemrID
  BEGIN
      -- call your sproc
  END


END