Cursor in SQL Server

Cursor is SQL data-type, this is used for saving a select result in order to seek through rows.
Cursor is a really useful guy, specially when you want to fetch records one-by-one, generally most of the database systems support cursor because it has defined in SQL ISO.

How to declare a cursor in SQL Server?

cursors are declared as same as variable declarations with a small difference, cursors variable doesn’t follow variable name in SQL, there is no @ symbol at first of cursor variables.

--forward-only cursor declaration, can just get the next result(record)
declare cur cursor for select * from dbo.a
--scrollable cursor declaration, can move freely through cursor
declare cur scroll cursor for select * from dbo.a

Unlike explicit transactions, by default cursors are alive until you deallocate(remove) them by deallocate command

--removing cursor c completely, it's better close it before deallocation
deallocate c;

for using a cursor, you need to open the cursor, fetch information and close it. A cursor has a pointer that points to the current row in the result, just after you open a cursor, pointer points to nothing (before first), so for fetching the first row, we need to fetch the next result(move the pointer). the global @@fetch_status variable is used for determining the current status of the cursor

  +------------------------------------------+
  |--> Before the result @@fetch_status = -1 |
  |-> D A T A - record  @@fetch_status = 0   |
  |-> D A T A - record  @@fetch_status = 0   |---\
  |-> D A T A - record  @@fetch_status = 0   |    \ A  Cursor
  |-> D A T A - record  @@fetch_status = 0   |    /  Context
  |-> D A T A - record  @@fetch_status = 0   |---/
  |-> D A T A - record  @@fetch_status = 0   |
  |--> After the result  @@fetch_status = -1 |
  +------------------------------------------+

Example

The following example tries to show folks table records in a specific format, as above diagram is showing, when a cursor get opened, the pointer is just before the first result, so we need to fetch next from the cursor, then iterate the cursor by a loop.

create table folks(id int identity(1,1),name varchar(32));
go
insert folks values('Arash'),('Pedram'),('Aidin'),('Farzad');
--step1:declare the cursor
declare cur scroll cursor for select * from folks;
declare @id int,@name varchar(32);
--step2:open the cursor
open cur;
--step3:fecth the very first record into variables 
fetch next from cur into @id,@name
print('--------------------------')
--step4:read the cursor to end
while( @@FETCH_STATUS=0 )
begin
	print(convert(varchar(10),@id)+space(10)+@name);
	fetch next from cur into @id,@name;
end
print('--------------------------');
--step5:close the cursor (close the handler)
close cur;
--step6:finilize the cursor
deallocate cur;
drop table folks;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s