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

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


Query Output Screen



* * * * *


Email Your Comment To AUTHOR