Cursor related statements

This book excerpt illustrates cursor statements, including the open statement, fetch statement, @@fetch_status, close statement and the deallocate statement for T-SQL cursors.

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.


This was first published in February 2007

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close