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
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 NEXT FROM EmployeeList
INTO @Emp_Code, @Emp_Name, @Salary, @Dept_Code, @Dept_Name
SELECT @Emp_Code, @Emp_Name, @Salary, @Dept_Code, @Dept_Name
CLOSE EmployeeList
DEALLOCATE EmployeeList
Query Output Screen
|