Programming in MS SQL Server 2012

Fetch Cursor

Fetch Cursor is used to retrieves a specific row from a Transact-SQL cursor variable.

Syntax

FETCH

[ [ NEXT | PRIOR | FIRST | LAST

| ABSOLUTE { n | @nvar }

| RELATIVE { n | @nvar }

]

FROM

]

{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }

[ INTO @variable_name [ ,...n ] ]

NEXT

Returns the result row immediately following the current row, and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.

PRIOR

Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.

FIRST

Returns the first row in the cursor and makes it the current row.

LAST

Returns the last row in the cursor and makes it the current row.

ABSOLUTE {n | @nvar}

If n or @nvar is positive, returns the row n rows from the front of the cursor and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows before the end of the cursor and makes the returned row the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.

RELATIVE {n | @nvar}

If n or @nvar is positive, returns the row n rows beyond the current row and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows prior to the current row and makes the returned row the new current row. If n or @nvar is 0, returns the current row. If FETCH RELATIVE is specified with n or @nvar set to negative numbers or 0 on the first fetch done against a cursor, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.

GLOBAL

Specifies that cursor_name refers to a global cursor.

cursor_name

Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.

@cursor_variable_name

Is the name of a cursor variable referencing the open cursor from which the fetch should be made.

INTO @variable_name[,...n]

Allows data from the columns of a fetch to be placed into local variables. Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.

The following example describes different FETCH data values from cursor variable.

-- Example 145 --

DECLARE EmployeeList SCROLL 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

OPEN EmployeeList

FETCH ABSOLUTE 5 FROM EmployeeList

FETCH FIRST FROM EmployeeList

FETCH NEXT FROM EmployeeList

FETCH PRIOR FROM EmployeeList

FETCH LAST FROM EmployeeList

FETCH RELATIVE -2 FROM EmployeeList

CLOSE EmployeeList

DEALLOCATE EmployeeList

Query Output Screen

The following example describes FETCH data values from cursor variable and then stores in local variables.

-- Example 146 --

DECLARE @Emp_Code int

DECLARE @Emp_Name varchar(50)

DECLARE @Salary money

DECLARE @Dept_Code int

DECLARE @Dept_Name varchar(50)

DECLARE EmployeeList CURSOR FOR