Cursor in SQL

DECLARE @Id int
DECLARE @name varchar(50)
DECLARE cur_emp CURSOR
STATIC FOR SELECT Sno,Name from EmpName
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
 FETCH NEXT FROM cur_emp INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 -- select @Id,@name
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name
 FETCH NEXT FROM cur_emp INTO @Id,@name
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp

Output :

ID : 1, Name : Sendhilkumar

ID : 2, Name : ArunKumar

ID : 3, Name : BBBB

Syntax to Declare Cursor

Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The basic syntax is given below

1.  DECLARE cursor_name CURSOR
2.  [LOCAL | GLOBAL] --define cursor scope
3.  [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
4.  [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
5.  [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
6.  FOR select_statement --define SQL Select statement
7.  FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

Syntax to Open Cursor

A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:

1.  OPEN [GLOBAL] cursor_name --by default it is local

Syntax to Fetch Cursor

Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:

1.  FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
2. FROM [GLOBAL] cursor_name
3. INTO @Variable_name[1,2,..n]

Syntax to Close Cursor

Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:

1.  CLOSE cursor_name --after closing it can be reopen

Syntax to Deallocate Cursor

Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:

1.  DEALLOCATE cursor_name --after deallocation it can't be reopen