Cursor in SQL

In this blog I am trying to explain the concept of Cursor in SQL Server.

Definition:

Cursor is an object of database which is used to retrieve data from result set row by row. In other words a cursor is a set of rows together with a pointer that identifies a current row. We use cursor when we need to update records in a table row by row.

Cursor Life Cycle:

1-Declare Cursor

A cursor is declared by DECLARE CURSOR SQL command. We can declare a cursor by following syntax:

DECLARE cur_name CURSOR [LOCAL | GLOBAL] --define the scope of cursor [FORWARD_ONLY | SCROLL] --define cursor movements [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- type of cursor [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks FOR select_statement -- SQL Select statement FOR UPDATE [column1,column2,...columnn] -- columns that to be updated

 

2-Open Cursor

A cursor can be open in two way either locally or globally. Syntax for open cursor:

OPEN [GLOBAL] cur_name --by default it is local

 

3-Fetch Cursor

After opening a you can fetch cursor. Syntax for fetch cursor:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n] FROM [GLOBAL] cur_name INTO @var1,@var2…..@varn

 

4-Close Cursor

Close statement close cursor explicitly. Syntax for close cursor:

CLOSE  cur_name

 

5-Deallocate Cursor

To free the resources allocated by cursor we deallocate the cursor.Syntax for deallocate cursor

DEALLCATE  cur_name

 

Example of Cursor

DECLARE @FirstName varchar(50)

DECLARE @LastName varchar(50)

DECLARE cur_name CURSOR

STATIC FOR SELECT FirstName,LastName FROM user1

OPEN cur_name

if @@CURSOR_ROWS>0

BEGIN

FETCH NEXT FROM cur_name INTO @FirstName,@LastName

WHILE @@FETCH_STATUS=0

BEGIN

PRINT 'First Name:'+@FirstName+',Last Name:'+@LastName

FETCH NEXT FROM cur_name INTO @FirstName,@LastName

END

END

CLOSE cur_name

DEALLOCATE cur_name