0Day Forums
SQL Call Stored Procedure for each Row without using a cursor - Printable Version

+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: SQL Call Stored Procedure for each Row without using a cursor (/Thread-SQL-Call-Stored-Procedure-for-each-Row-without-using-a-cursor)

Pages: 1 2

SQL Call Stored Procedure for each Row without using a cursor - hypertragically839984 - 07-31-2023

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?

RE: SQL Call Stored Procedure for each Row without using a cursor - mandaaprgqrwi - 07-31-2023

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 ?


RE: SQL Call Stored Procedure for each Row without using a cursor - greyv - 07-31-2023

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




RE: SQL Call Stored Procedure for each Row without using a cursor - angeline801659 - 07-31-2023

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.

RE: SQL Call Stored Procedure for each Row without using a cursor - fiorazuw - 07-31-2023

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.

RE: SQL Call Stored Procedure for each Row without using a cursor - annexing639706 - 07-31-2023

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

- [Using CROSS APPLY in SQL Server 2005][2]

Just for clarity, I'm referring to those cases where the stored procedure can be converted into a table valued function.


[1]:

[To see links please register here]

[2]:

[To see links please register here]



RE: SQL Call Stored Procedure for each Row without using a cursor - dace452 - 07-31-2023

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.

RE: SQL Call Stored Procedure for each Row without using a cursor - surrendering364291 - 07-31-2023

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;

RE: SQL Call Stored Procedure for each Row without using a cursor - katinealeitmymw - 07-31-2023

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

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.

RE: SQL Call Stored Procedure for each Row without using a cursor - Sirtachymetryfsm - 07-31-2023

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