In this article 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

 

  Modified On Nov-21-2017 12:13:44 PM

Leave Comment