Cursors in tsql, it can be done …

Simple cursor loop

Example below is the most straight forward example of walking through a CURSOR with a WHILE loop in tsql. The cursor is declared, opened and and a FETCH NEXT is done while @@fetch_status equals 0.

The behaviour of a FETCH [ NEXT ] call is that it returns the value(s) for the column(s) specified in the DECLARE CURSOR as a single-row result set.

Basic cursor example with FETCH returning row as single-row result set

1> declare c1 cursor
2> for
3> select left( name, 32)            as name,
4>        cast( dbid as varchar(10)) as dbid
5>   from master.dbo.sysdatabases
6>  where dbid < 5
7>
8> open c1
9> fetch next from c1
10>
11> -- while @@fetch_status != -1 and @@fetch_status != -2
12> while @@fetch_status = 0
13>    fetch next from c1
14>
15> close c1
16> deallocate c1
17>
18> go
 name                             dbid
 -------------------------------- ----------
 master                           1
 
 name                             dbid
 -------------------------------- ----------
 tempdb                           2
 
 name                             dbid
 -------------------------------- ----------
 model                            3
 
 name                             dbid
 -------------------------------- ----------
 msdb                             4
 
 name                             dbid
 -------------------------------- ----------
 
1>

@@fetch_status

@@fetch_status is a built in variable returning the exit code of the latest fetch call.

Return Value	Description
0	FETCH statement was successful.
-1	FETCH statement failed or the row was beyond the result set.
-2	Row fetched is missing.

FETCH INTO

The ‘FETCH INTO’ option is used to return values from a cursor (instead of getting it returned as a single-row result set) into pre-defined variables.

Example below displays how to perform a FETCH INTO with a single column.

1> declare @s_name    varchar(32)
2> declare c1 cursor
3> for
4> select name
5>   from master.dbo.sysdatabases
6>  where dbid < 5
7>
8> open c1
9> fetch next from c1 into @s_name
10>
11> while @@fetch_status = 0
12> begin
13>   print 'name = ' + @s_name
14>   fetch next from c1 into @s_name
15> end
16>
17> close c1
18> deallocate c1
19>
20> go
name = master
name = tempdb
name = model
name = msdb
1>

Example below displays how to perform a FETCH INTO with multiple columns.

1> declare @s_name    varchar(32),
2>         @i_dbid    smallint
3> declare c1 cursor
4> for
5> select name,
6>        dbid
7>   from master.dbo.sysdatabases
8>  where dbid < 5
9>
10> open c1
11> fetch next from c1 into @s_name, @i_dbid
12>
13> while @@fetch_status = 0
14> begin
15>   print 'DB = ' + cast( @i_dbid as varchar(8)) + ':' + @s_name
16>   fetch next from c1 into @s_name, @i_dbid
17> end
18>
19> close c1
20> deallocate c1
21>
22> go
DB = 1:master
DB = 2:tempdb
DB = 3:model
DB = 4:msdb
1>

Scrollable cursors

All examples above are so-called ‘forward only cursors’ (the default cursor type). When you want to be able to scroll through your cursor the cursor most be declared through the SCROLL CURSOR declaration.

Whenever a cursor is declared as a SCROLL CURSOR the following FETCH options become available:

Fetch Options

• PRIOR
• FIRST
• LAST
• ABSOLUTE { n | @nvar }
• RELATIVE { n | @nvar }

All examples below are done with ‘demo’ table listed below.

demo table

1> select * from demo order by k
2> go
 k
 -----------
           0
           1
           2
           3
           4
           5
           6
           7
           8
           9
 
(10 rows affected)
1>

FETCH NEXT and FETCH PRIOR

1> declare @i as int
2> declare c1 scroll cursor
3> for
4> select k
5>   from demo
6> order by k
7>
8>
9>
10> open c1
11>
12>
13> print 'FETCH NEXT ' + char(10)
14> print '=================================='
15>
16> fetch next from c1 into @i
17> while @@fetch_status = 0
18> begin
19>   print cast(@i as char(1)) + ' @@fetch_status ' + cast(@@fetch_status as char)
20>   fetch next from c1 into @i
21> end
22>
23> fetch next from c1 into @i
24> print cast(@i as char(1)) + ' @@fetch_status ' + cast(@@fetch_status as char)
25>
26>
27> print char(10) + 'FETCH PRIOR ' + char(10)
28> print '=================================='
29>
30> fetch prior from c1 into @i
31> while @@fetch_status = 0
32> begin
33>   print cast(@i as char(1)) + ' @@fetch_status ' + cast(@@fetch_status as char)
34>   fetch prior from c1 into @i
35> end
36>
37> fetch prior from c1 into @i
38> print cast(@i as char(1)) + ' @@fetch_status ' + cast(@@fetch_status as char)
39>
40>
41> close c1
42> deallocate c1
43>
44>
45>
46> go
FETCH NEXT
==================================
0 @@fetch_status 0
1 @@fetch_status 0
2 @@fetch_status 0
3 @@fetch_status 0
4 @@fetch_status 0
5 @@fetch_status 0
6 @@fetch_status 0
7 @@fetch_status 0
8 @@fetch_status 0
9 @@fetch_status 0
9 @@fetch_status -1
 
FETCH PRIOR
==================================
9 @@fetch_status 0
8 @@fetch_status 0
7 @@fetch_status 0
6 @@fetch_status 0
5 @@fetch_status 0
4 @@fetch_status 0
3 @@fetch_status 0
2 @@fetch_status 0
1 @@fetch_status 0
0 @@fetch_status 0
0 @@fetch_status -1
1>

FETCH LAST / FETCH PRIOR

1> declare @i as int
2> declare c1 scroll cursor
3> for
4> select k
5>   from demo
6> order by k
7>
8>
9>
10> open c1
11>
12>
13> print char(10) + 'FIRST LAST PRIOR ' + char(10)
14> print '=================================='
15> fetch last from c1 into @i
16> print 'FETCH LAST  = ' + cast(@i as char(1)) +
17>         ' @@fetch_status = ' + cast(@@fetch_status as char)
18> fetch prior from c1 into @i
19> print 'FETCH PRIOR = ' + cast(@i as char(1)) +
20>         ' @@fetch_status = ' + cast(@@fetch_status as char)
21> fetch first from c1 into @i
22> print 'FETCH FIRST = ' + cast(@i as char(1)) +
23>         ' @@fetch_status = ' + cast(@@fetch_status as char)
24> fetch prior from c1 into @i
25> print 'FETCH PRIOR = ' + cast(@i as char(1)) +
26>         ' @@fetch_status = ' + cast(@@fetch_status as char)
27> fetch next from c1 into @i
28> print 'FETCH NEXT  = ' + cast(@i as char(1)) +
29>         ' @@fetch_status = ' + cast(@@fetch_status as char)
30> fetch next from c1 into @i
31> print 'FETCH NEXT  = ' + cast(@i as char(1)) +
32>         ' @@fetch_status = ' + cast(@@fetch_status as char)
33>
34>
35> close c1
36> deallocate c1
37>
38>
39>
40> go
 
FIRST LAST PRIOR
==================================
FETCH LAST  = 9 @@fetch_status = 0
FETCH PRIOR = 8 @@fetch_status = 0
FETCH FIRST = 0 @@fetch_status = 0
FETCH PRIOR = 0 @@fetch_status = -1
FETCH NEXT  = 0 @@fetch_status = 0
FETCH NEXT  = 1 @@fetch_status = 0
1>

FETCH ABSOLUTE

1> declare @i as int
2> declare c1 scroll cursor
3> for
4> select k
5>   from demo
6> order by k
7>
8> open c1
9>
10> print char(10) + 'FETCH ABSOLUTE ' + char(10)
11> print '======================================'
12> fetch absolute 2 from c1 into @i
13> print 'FETCH ABSOLUTE 2 = ' + cast(@i as char(1)) +
14>         ' @@fetch_status = ' + cast(@@fetch_status as char)
15> fetch absolute 8 from c1 into @i
16> print 'FETCH ABSOLUTE 8 = ' + cast(@i as char(1)) +
17>         ' @@fetch_status = ' + cast(@@fetch_status as char)
18>
19> close c1
20> deallocate c1
21>
22> go
 
FETCH ABSOLUTE
======================================
FETCH ABSOLUTE 2 = 1 @@fetch_status = 0
FETCH ABSOLUTE 8 = 7 @@fetch_status = 0
1>

FETCH RELATIVE

1> declare @i as int
2> declare c1 scroll cursor
3> for
4> select k
5>   from demo
6> order by k
7>
8>
9>
10> open c1
11>
12>
13> print char(10) + 'FETCH RELATIVE' + char(10)
14> print '======================================='
15> fetch relative 1 from c1 into @i
16> print 'FETCH RELATIVE 1   = ' + cast( @i as varchar(1)) +
17>         ' @@fetch_status = ' + cast(@@fetch_status as char)
18>
19> fetch relative -1 from c1 into @i
20> print 'FETCH RELATIVE -1  = ' + cast( @i as varchar(1)) +
21>         ' @@fetch_status = ' + cast(@@fetch_status as char)
22>
23> fetch relative -2 from c1 into @i
24> print 'FETCH RELATIVE -2  = ' + cast( @i as varchar(1)) +
25>         ' @@fetch_status = ' + cast(@@fetch_status as char)
26>
27> fetch relative 3 from c1 into @i
28> print 'FETCH RELATIVE 3   = ' + cast( @i as varchar(1)) +
29>         ' @@fetch_status = ' + cast(@@fetch_status as char)
30>
31> fetch relative 100 from c1 into @i
32> print 'FETCH RELATIVE 100 = ' + cast( @i as varchar(1)) +
33>         ' @@fetch_status = ' + cast(@@fetch_status as char)
34>
35> fetch relative -1 from c1 into @i
36> print 'FETCH RELATIVE -1  = ' + cast( @i as varchar(1)) +
37>         ' @@fetch_status = ' + cast(@@fetch_status as char)
38>
39>
40> close c1
41> deallocate c1
42>
43>
44>
45> go
 
FETCH RELATIVE
=======================================
FETCH RELATIVE 1   = 0 @@fetch_status = 0
FETCH RELATIVE -1  = 0 @@fetch_status = -1
FETCH RELATIVE -2  = 0 @@fetch_status = -1
FETCH RELATIVE 3   = 2 @@fetch_status = 0
FETCH RELATIVE 100 = 2 @@fetch_status = -1
FETCH RELATIVE -1  = 9 @@fetch_status = 0
1>