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>