LLI Wiki — My Digital Knowledge Base

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

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

Mastodon LLIMastodon SocialFosstodon