Relational databases are designed to work with sets of data. In fact, the purpose of the Select statement, as the most important statement in SQL, is to define a set of records. In contrast, end-user applications display information to the user record by record (or maybe in small batches). To close the gap between these conflicting requirements, RDBMS architects have invented a new class of programming constructs—cursors.
Many types of cursors are implemented in various environments using different syntax, but all cursors work in a similar fashion:
1. A cursor first has to be defined and its features have to be set.
2. The cursor must be populated.
3. The cursor has to be positioned (scrolled) to a record or block of records that needs to be retrieved (fetched).
4. Information from one or more current records is fetched, and then some modification can be performed or some action can be initiated based on the fetched information.
5. Optionally, steps 3 and 4 are repeated.
6. Finally, the cursor must be closed and resources released.
Cursors can be used on both server and client sides. SQL Server and the APIs for accessing database information (OLE DB, ODBC, DB-Library) all include sets of functions for processing cursors.
SQL Server supports three classes of cursors:
The major difference between Transact-SQL cursors and other types of cursors is their purpose. Transact-SQL cursors are used from stored procedures, batches, functions, or triggers to repeat custom processing for each row of the cursor. Other kinds of cursors are designed to access database information from the client application. We will review only Transact-SQL cursors.
Processing in Transact-SQL cursors has to be performed in the following steps:
1. Use the Declare Cursor statement to create the cursor based on the Select statement.
2. Use the Open statement to populate the cursor.
3. Use the Fetch statement to change the current record in the cursor and to store values into local variables.
4. Do something with the retrieved information.
5. If needed, repeat steps 3 and 4.
6. Use the Close statement to close the cursor. Most of the resources (memory, locks, and so on) will be released.
7. Use the Deallocate statement to deallocate the cursor.
Transact-SQL cursors do not support processing blocks of records. Only one record can be fetched at a time.
It is best to show this process through an example. We will rewrite the stored procedure that we used to illustrate the use of the While statement. The purpose of this stored procedure is to collect the properties of a specified asset and return them in delimited format (Property = Value Unit;). The final result should look like this:
CPU=Pentium II;RAM=64 MB;HDD=6.4 GB;Resolution=1024x768;Weight=2 kg;
Here is the code for the new instance of the stored procedure:
Alter Procedure dbo.ap_InventoryProperties_Get_Cursor /******************************************************************** Return comma-delimited list of properties that are describing asset. Property = Value unit;Property = Value unit;Property = Value unit;... Output: @chvProperties Return: n/a Name Date Description Created by: Dejan Sunderic 2005.04.18 Modified by: test: declare @p varchar(max) exec dbo.ap_InventoryProperties_Get_Cursor 5, @p OUTPUT, 1 select @p ********************************************************************/ ( @intInventoryId int, @chvProperties varchar(max) OUTPUT, @debug int = 0 ) As declare @intCountProperties int, @intCounter int, @chvProperty varchar(50), @chvValue varchar(50), @chvUnit varchar(50) Set @chvProperties = '' Declare @CrsrVar Cursor Set @CrsrVar = Cursor For select Property, Value, Unit from dbo.InventoryProperty InventoryProperty inner join dbo.Property Property on InventoryProperty.PropertyId = Property.PropertyId where InventoryProperty.InventoryId = @intInventoryId Open @CrsrVar Fetch Next From @CrsrVar Into @chvProperty, @chvValue, @chvUnit While (@@FETCH_STATUS = 0) Begin Set @chvUnit = Coalesce(@chvUnit, '') If @debug <> 0 Select @chvProperty Property, @chvValue [Value], @chvUnit [Unit] -- assemble list Set @chvProperties = @chvProperties + @chvProperty + '=' + @chvValue + ' ' + @chvUnit + '; ' If @debug <> 0 Select @chvProperties chvProperties Fetch Next From @CrsrVar Into @chvProperty, @chvValue, @chvUnit End Close @CrsrVar Deallocate @CrsrVar Return 0
The stored procedure will first declare a cursor:
Declare @CrsrVar Cursor
The cursor will then be associated with the collection of properties related to the specified asset:
Set @CrsrVar = Cursor For Select Property, Value, Unit From dbo.InventoryProperty InventoryProperty inner join dbo.Property Property On InventoryProperty.PropertyId = Property.PropertyId Where InventoryProperty.InventoryId = @intInventoryId
Before it can be used, the cursor needs to be opened:
The content of the first record can then be fetched into local variables:
Fetch Next From @CrsrVar Into @chvProperty, @chvValue, @chvUnit
While (@@FETCH_STATUS = 0)
After the values from the first record are processed, we read the next record:
Fetch Next From @CrsrVar Into @chvProperty, @chvValue, @chvUnit
Once all records have been read, the value of @@fetch_status is set to –1 and we exit the loop. We need to close and deallocate the cursor and finish the stored procedure:
Close @CrsrVar Deallocate @CrsrVar
Now, let's save and execute this stored procedure:
Declare @chvRes varchar(max) Exec ap_InventoryProperties_Get_Cursor 5, @chvRes OUTPUT Select @chvRes Properties
SQL Server will return the following:
Properties -------------------------------------------------------------------- CPU=Pentium II ; RAM=64 MB; HDD=6.4 GB; Resolution=1024x768 ; Weight =2 kg; Clock=366 MHz;
Basic Transact-SQL Programming Constructs
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.