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

I usually do it this way when it's a quite a few rows:

1. Select all sproc parameters in a dataset with SQL Management Studio
2. Right-click -> Copy
3. Paste in to excel
4. Create single-row sql statements with a formula like '="EXEC schema.mysproc @param=" & A2' in a new excel column. (Where A2 is your excel column containing the parameter)
5. Copy the list of excel statements into a new query in SQL Management Studio and execute.
6. Done.

(On larger datasets i'd use one of the solutions mentioned above though).

Answer 2

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.

Answer 3

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 > 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 > LastGameID
         ORDER BY id LIMIT 0,1;
    END WHILE;
    SET output = "done";
END;//

CALL setFakeUsers(@status);
SELECT @status;

Answer 4

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 & 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 > @CustomerId 
  ORDER BY CustomerID

  -- Exit loop if no more customers
  IF @@ROWCOUNT = 0 BREAK;

  -- call your sproc
  EXEC dbo.YOURSPROC @CustomerId

END

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

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