How to Iterate Through T-SQL Query Results Using Cursors?

Looping through Query Results in T-SQL

To loop over the results of the query SELECT @id=table.id FROM table, you can utilize a CURSOR in T-SQL. Here's a code snippet that demonstrates how to achieve this:

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:

• A CURSOR named @getid is created to iterate through the results of the query.
• The OPEN statement executes the query and assigns the results to the cursor.
• The FETCH NEXT statement retrieves the first row of results and assigns the values to the variables @id and @name.
• The WHILE loop continues fetching rows from the cursor until there are no more rows to process.
• For each row, the EXEC statement executes the stored procedure stored_proc with the parameters @varName, @otherVarName, and @varForName set to the corresponding values from the table.
• After all rows have been processed, the CLOSE and DEALLOCATE statements close and remove the cursor from memory.

The above is the detailed content of How to Iterate Through T-SQL Query Results Using Cursors?. For more information, please follow other related articles on the PHP Chinese website!