Example
A simple cursor syntax, operating on a few example test rows:
/* Prepare test data */
DECLARE @test_table TABLE
(
Id INT,
Val VARCHAR(100)
);
INSERT INTO @test_table(Id, Val)
VALUES
(1, 'Foo'),
(2, 'Bar'),
(3, 'Baz');
/* Test data prepared */
/* Iterator variable @myId, for example sake */
DECLARE @myId INT;
/* Cursor to iterate rows and assign values to variables */
DECLARE myCursor CURSOR FOR
SELECT Id
FROM @test_table;
/* Start iterating rows */
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myId;
/* @@FETCH_STATUS global variable will be 1 / true until there are no more rows to fetch */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Write operations to perform in a loop here. Simple SELECT used for example */
SELECT Id, Val
FROM @test_table
WHERE Id = @myId;
/* Set variable(s) to the next value returned from iterator; this is needed otherwise the cursor will loop infinitely. */
FETCH NEXT FROM myCursor INTO @myId;
END
/* After all is done, clean up */
CLOSE myCursor;
DEALLOCATE myCursor;
Results from SSMS. Note that these are all separate queries, they are in no way unified. Notice how the query engine processes each iteration one by one instead of as a set.
| Id | Val |
|---|
| 1 | Foo |
| (1 row(s) affected) | |
| Id | Val |
|---|
| 2 | Bar |
| (1 row(s) affected) | |
| Id | Val |
|---|
| 3 | Baz |
| (1 row(s) affected) | |
