Programming in MS SQL Server 2012

Declare Cursor

A cursor can be declares in two different methods. DECLARE CURSOR and then SET the result set in the @local_variable or else DECLARE CURSOR along with @local_variable.
Syntax

ISO Syntax

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

     
FOR select_statement

     
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

[;]

Transact-SQL Extended Syntax

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

     
[ FORWARD_ONLY | SCROLL ]

     
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

     
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

     
[ TYPE_WARNING ]

     
FOR select_statement

     
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

The following example describes DECLARE CURSOR and then SET method.

-- Example 142 --

DECLARE @MyVariable CURSOR

DECLARE MyCursor CURSOR FOR

SELECT

EMP.Emp_Code,

EMP.Emp_Name,

EMP.Salary,

EMP.Dept_Code,

DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

SET @MyVariable = MyCursor

Query Output Screen

The following example describes DECLARE CURSOR along with @local_variable.

-- Example 143 --

DECLARE @MyVariable CURSOR

SET @MyVariable = CURSOR SCROLL KEYSET FOR