FETCH (Transact-SQL)

SQL Server 2005 books online (September 2007) FETCH (Transact-SQL)

You can use a Transact-SQL Server cursor to retrieve specific rows.

Syntax

FETCH          [ [ NEXT | PRIOR | FIRST | LAST                    | ABSOLUTE { n | @nvar }                    | RELATIVE { n | @nvar }               ]               FROM          ]{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }[ INTO @variable_name [ ,...n ] ] 

Parameters

NEXT

The result line is returned immediately after the current row, and the current row increments to the return row. If fetch next is the first extraction operation on the cursor, the first row in the result set is returned. NEXT is the default cursor extraction option.

PRIOR

Returns the result row next to the current row, and the current row decreases to the return row. If fetch prior is the first extraction operation on the cursor, no rows are returned and the cursor is placed before the first row.

FIRST

Returns the first row in the cursor and uses it as the current row.

LAST

Returns the last row in the cursor and uses it as the current row.

ABSOLUTE { N| @ Nvar}

IfNOr@ NvarReturns the nth value starting from the cursor header.NAnd change the returned row to the new current row. IfNOr@ NvarIf it is a negative number, returnNAnd change the returned row to the new current row. IfNOr@ NvarIf the value is 0, no rows are returned.NMust be an integer constant and@ NvarThe data type of must beSmallint,TinyintOrInt.

RELATIVE { N| @ Nvar}

IfNOr@ NvarReturns the nth value starting from the current row.NAnd change the returned row to the new current row. IfNOr@ NvarIf it is a negative number, returnNAnd change the returned row to the new current row. IfNOr@ NvarIf the value is 0, the current row is returned. When the cursor is extracted for the first timeNOr@ NvarIf fetch relative is set to negative or 0, no row is returned.NMust be an integer constant,@ NvarThe data type of must beSmallint,TinyintOrInt.

GLOBAL

SpecifyCursor_nameGlobal cursor.

Cursor_name

Name of the open cursor to be extracted from. If bothCursor_nameA global or local cursor with the name exists. If GLOBAL is specifiedCursor_nameIt refers to a GLOBAL cursor. If GLOBAL is not specified, it refers to a local cursor.

@ Cursor_variable_name

The name of the cursor variable that refers to the open cursor from which the extraction operation is to be performed.

INTO @ Variable_name[ , ... N]

You can place the extracted column data in a local variable. The variables in the list are associated with the corresponding columns in the cursor result set from left to right. The data type of each variable must match the data type of the corresponding result set column, or the implicit conversion supported by the data type of the result set column. The number of variables must be the same as the number of columns in the cursor selection list.

Remarks

If the SCROLL option is not specified in the declare cursor statement of the SQL-92 style, NEXT is the only supported FETCH option. All FETCH options are supported if the SCROLL option is specified in the declare cursor statement for the SQL-92 style.

If you use the Transact-SQL DECLARE cursor extension plug-in, apply the following rules:

• If FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only supported FETCH option.
• If the DYNAMIC, FORWARD_ONLY, or FAST_FORWARD option is not specified, and one of KEYSET, STATIC, or SCROLL is specified, all FETCH options are supported.
• Dynamic scroll cursor supports all FETCH options except ABSOLUTE.

@ FETCH_STATUS the function reports the status of the previous FETCH statement. The same information is recorded inSp_describe_cursorIn the returned cursorFetch_statusColumn. These status information should be used to determine the validity of the data before any operations are performed on the data returned by the FETCH statement. For more information, see http://technet.microsoft.com/zh-cn/library/ms187308.aspx.

Permission

The FETCH permission is granted to any valid user by default.

Example

A. Use FETCH in A simple cursor

The following example showsPerson. ContactThe rows whose surnames start with B in the table declare a simple cursor and extract these rows one by one using FETCH NEXT. The FETCH statement returns the value of the specified column in The declare cursor in the form of a single row result set.

Copy code

USE AdventureWorksGODECLARE contact_cursor CURSOR FORSELECT LastName FROM Person.ContactWHERE LastName LIKE 'B%'ORDER BY LastNameOPEN contact_cursor-- Perform the first fetch.FETCH NEXT FROM contact_cursor-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM contact_cursorENDCLOSE contact_cursorDEALLOCATE contact_cursorGO

B. Use FETCH to store values into Variables

The following example is similar to the preceding example, but the output of the FETCH statement is stored in local variables rather than directly returned to the client. The PRINT statement combines variables into a single string and returns them to the client.

Copy code

USE AdventureWorksGO-- Declare the variables to store the values returned by FETCH.DECLARE @LastName varchar(50), @FirstName varchar(50)DECLARE contact_cursor CURSOR FORSELECT LastName, FirstName FROM Person.ContactWHERE LastName LIKE 'B%'ORDER BY LastName, FirstNameOPEN contact_cursor-- Perform the first fetch and store the values in variables.-- Note: The variables are in the same order as the columns-- in the SELECT statement.FETCH NEXT FROM contact_cursorINTO @LastName, @FirstName-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN-- Concatenate and display the current values in the variables.PRINT 'Contact Name: ' + @FirstName + ' ' +  @LastName-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM contact_cursorINTO @LastName, @FirstNameENDCLOSE contact_cursorDEALLOCATE contact_cursorGO

C. Declare the SCROLL cursor and use other FETCH options

The following example creates a SCROLL cursor so that it supports the full SCROLL function through the LAST, PRIOR, RELATIVE, and ABSOLUTE options.

Copy code

USE AdventureWorksGO-- Execute the SELECT statement alone to show the-- full result set that is used by the cursor.SELECT LastName, FirstName FROM Person.ContactORDER BY LastName, FirstName-- Declare the cursor.DECLARE contact_cursor SCROLL CURSOR FORSELECT LastName, FirstName FROM Person.ContactORDER BY LastName, FirstNameOPEN contact_cursor-- Fetch the last row in the cursor.FETCH LAST FROM contact_cursor-- Fetch the row immediately prior to the current row in the cursor.FETCH PRIOR FROM contact_cursor-- Fetch the second row in the cursor.FETCH ABSOLUTE 2 FROM contact_cursor-- Fetch the row that is three rows after the current row.FETCH RELATIVE 3 FROM contact_cursor-- Fetch the row that is two rows prior to the current row.FETCH RELATIVE -2 FROM contact_cursorCLOSE contact_cursorDEALLOCATE contact_cursorGO

See

Reference

CLOSE (Transact-SQL)
DEALLOCATE (Transact-SQL)
Declare cursor (Transact-SQL)
OPEN (Transact-SQL)
Other resources

Transact-SQL cursor

Help and information

Obtain SQL Server 2005 help