How Can I Iterate Through T-SQL Query Results and Execute a Stored Procedure for Each Row?

T-SQL Looping through Query Results

Suppose you have a query that retrieves a list of IDs from a table:

SELECT @id=table.id FROM table

You then need to execute a stored procedure for each row, passing in the ID and another value:

EXEC stored_proc @varName=@id, @otherVarName='test'

In T-SQL, you can accomplish this using a CURSOR:

DECLARE @id INT
DECLARE @name NVARCHAR(100)
DECLARE @getid CURSOR

SET @getid = CURSOR FOR
SELECT table.id,
       table.name
FROM   table

OPEN @getid
FETCH NEXT
FROM @getid INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC stored_proc @varName=@id, @otherVarName='test', @varForName=@name
    FETCH NEXT
    FROM @getid INTO @id, @name
END

CLOSE @getid
DEALLOCATE @getid

In this script:

• We declare three variables: @id (INT), @name (NVARCHAR(100)), and a CURSOR named @getid.
• We set @getid to a CURSOR that selects the ID and name columns from the table table.
• We open the CURSOR, fetch the first row, and store the results in @id and @name.
• We enter a WHILE loop that continues as long as the @@FETCH_STATUS value is 0 (indicating that a row was successfully fetched).
• Inside the loop, we execute the stored procedure, passing in @id, the literal value 'test' for the @otherVarName parameter, and the fetched name value for the @varForName parameter.
• We fetch the next row and store the results in @id and @name.
• When there are no more rows to fetch, we close and deallocate the CURSOR.

This script allows you to loop through the query results and execute the stored procedure for each row, updating the variable names and values based on the data in the query.

The above is the detailed content of How Can I Iterate Through T-SQL Query Results and Execute a Stored Procedure for Each Row?. For more information, please follow other related articles on the PHP Chinese website!