Cursor-related Statements and Functions
Let's review statements and functions that you need to utilize to control cursors.
The Declare Cursor Statement
The Declare Cursor statement declares the Transact-SQL cursor and specifies its behavior and the query on which it is built. It is possible to use syntax based on the SQL-92 standard or native Transact-SQL syntax. I will display only the simplified syntax. If you need more details, refer to SQL Server Books OnLine.
Declare cursor_name Cursor
For select_statement
The name of the cursor is an identifier that complies with the rules set for local variables.
The Open Statement
The Open statement executes the Select statement specified in the Declare Cursor statement and populates the cursor:
Open { { [Global] cursor_name } | cursor_variable_name}
The Fetch Statement
The Fetch statement reads the row specified in the Transact-SQL cursor:
Fetch [ [ Next | Prior | First | Last
| Absolute {n | @nvar}
| Relative {n | @nvar}
]
From
]
{ { [Global] cursor_name } | @cursor_variable_name}
[Into @variable_name[,...n] ]
This statement can force the cursor to position the current record at the Next, Prior, First, or Last record. It is also possible to specify the Absolute position of the record or a position Relative to the current record.
If the developer specifies a list of global variables in the Into clause, those variables will be filled with values from the specified record.
If the cursor has just been opened, you can use Fetch Next to read the first record.
@@ fetch_status
@@fetch_status is a function (or global variable) that returns the success code of the last Fetch statement executed during the current connection. It is often used as an exit criterion in loops that fetch records from a cursor.
@@cursor_rows
As soon as the cursor is opened, the @@cursor_rows function (or global variable) is set to the number of records in the cursor (you can use this variable to loop through the cursor also).
When the cursor is of a dynamic or keyset type, the @@cursor_rows function will be set to a negative number to indicate it is being asynchronously populated.
The Close Statement
The Close statement closes an open cursor, releases the current recordset, and releases locks on rows held by the cursor:
Close { { [Global] cursor_name } | cursor_variable_name }
This statement must be executed on an opened cursor. If the cursor has just been declared, SQL Server will report an error.
The Deallocate Statement
After the Close statement, the structure of the cursor is still in place. It is possible to open it again. If you do not plan to use it anymore, you should remove the structure as well, by using the Deallocate statement:
Deallocate { { [Global] cursor_name } | @cursor_variable_name}
Basic Transact-SQL Programming Constructs
Home: Introduction
Tip 1: T-SQL identifiers
Tip 2: Database object qualifiers
Tip 3: Character string data types
Tip 4: Date, time and number data types
Tip 5: Special data types-Part 1
Tip 6: Special data types-Part 2
Tip 7: Local variables
Tip 8: Global variables
Tip 9: Table variables
Tip 10: Flow control Statements
Tip 11: Blocks, looping, and conditional statements
Tip 12: Unconditional and scheduled execution
Tip 13: Transact SQL cursors
Tip 14: Cursor related statements
Tip 15: Problems and justified use of cursors
The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.
