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 |


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));
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
--step4:read the cursor to end
while( @@FETCH_STATUS=0 )
	fetch next from cur into @id,@name;
--step5:close the cursor (close the handler)
close cur;
--step6:finilize the cursor
deallocate cur;
drop table folks;

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s