Sql Server FOR EACH Loop

sql

set-based-operations

recursive-cte

execution-plans

byAlex Kataev·Oct 19, 2024

Iterate over rows in SQL Server using a WHILE loop with a cursor. Here's a prototype:

DECLARE @Id INT
DECLARE @Cursor CURSOR = CURSOR FOR SELECT Id FROM YourTable

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Insert your SQL operation using @Id here (e.g., UPDATE, DELETE)
    FETCH NEXT FROM @Cursor INTO @Id
END

CLOSE @Cursor
DEALLOCATE @Cursor

This script conjures a cursor to traverse YourTable, harnesses a WHILE loop to process each Id, and marks a placeholder for your applicable SQL command.

Let the `SET` take control: towards set-oriented SQL operations

Throw out the old school cursors and WHILE loop thinking, and move towards set-based operations. SQL Server loves dealing with full sets of data at once, and your performance will be dancing the Cha-Cha in happiness.

Crafting a date series with recursive CTE

WITH DateSeries AS (
    SELECT CAST('2021-01-01' AS DATE) AS DateValue  -- "Casting" isn't just for movie directors
    UNION ALL
    SELECT DATEADD(day, 1, DateValue)  -- Adds a day faster than 24 Jack Bauer hours
    FROM DateSeries
    WHERE DateValue < '2021-01-31'  -- When January tries to overstay its welcome
)
SELECT * FROM DateSeries
OPTION (MAXRECURSION 0)  -- Recursion: because SQL has a 'loop' hole

This recursive CTE generates a sequence of dates, one day at a time. The MAXRECURSION option, set to 0, allows unlimited recursion - it's an all you can iterate buffet.

Creating number tables for dynamic iteration

Sometimes you need to generate a series of numbers for any purpose like a kid counting chocolates. A numbers table or a recursive CTE, can be a magic wand in those situations.

Conjure dates with a numbers table

-- If you have prepared a number table
SELECT n.Number, DATEADD(day, n.Number, '2021-01-01')
FROM TallyTable as n
WHERE n.Number between 0 and 30  -- No, 30 is not always the limit!

Exploring further: Temp tables, table variables, and cursors

A table variable or temporary table can closely mimic a loop's functionality and stash iterative results.

Stowing results in a table variable

DECLARE @Result TABLE (ResultDate DATE)

INSERT INTO @Result (ResultDate)
SELECT DATEADD(day, s.number, @StartDate)
FROM master..spt_values s
WHERE type = 'P' AND s.number BETWEEN 0 AND DATEDIFF(day, @StartDate, @EndDate) -- Just a casual day at the BETWEEN

Reevaluating cursors for complex cases

Rarely will you find yourself in a scenario where procedural handling, like a cursor, is a must-have - like a coffee in the morning for some of us! On rare occasions when stars align, doing so can actually be justifiable:

Using cursors when needed

DECLARE myCursor CURSOR FOR 
SELECT complexCalculation FROM myTable WHERE conditionMet = 1

OPEN myCursor
FETCH NEXT FROM myCursor INTO @MyVariable

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Insert necessary operations here
    FETCH NEXT FROM myCursor INTO @MyVariable
END

CLOSE myCursor
DEALLOCATE myCursor

Tweaking and optimising

Before going live, check under the hood - execution plans - to know you're driving on the optimal path. It's the pit stop that saves you from any upcoming crash: