Looping over rows with a T-SQL cursor
Declare, open, fetch and close a simple T-SQL cursor, with the loop driven by @@FETCH_STATUS.
A cursor processes the rows of a result set one at a time. The example below declares a cursor, fetches each row in a loop and closes it.
Declaration options
The source lists this declaration form:
DECLARE cursor_name CURSOR LOCAL STATIC
LOCAL: the cursor name is visible only in the batch, stored procedure or trigger that declares it.STATIC: the cursor works on a snapshot of the result set.
Simple example
declare @fild1 varchar(100)
declare cur_select cursor for select 1
open cur_select
fetch cur_select into @fild1
while (@@FETCH_STATUS = 0)
begin
print @fild1
fetch cur_select into @fild1
end
close cur_select
deallocate cur_select
The loop runs while @@FETCH_STATUS is 0, which means the last fetch returned a row. The cursor is closed with CLOSE and removed with DEALLOCATE.
Notes
- Cursors handle one row at a time. For large sets, a set-based statement usually performs better for the same result.