SQL 2005+SQL Server Cursors (2)
The fifty-sixth part of the SQL Server Programming Fundamentals tutorial examines the use of cursors in Transact-SQL (T-SQL). Cursors are database objects that allow a data set that has been generated using a query to be processed a single row at a time.
Opening a Cursor
A cursor cannot be used until it has been opened using the OPEN statement. On opening, the query is executed and the cursor is populated. To open the billing cursor, use the following:
Once a cursor is opened you can determine the number of rows it has using the @@CURSOR_ROWS function. This function returns the number of rows of the latest cursor to be opened.
PRINT @@CURSOR_ROWS -- Outputs "4"
Fetching the Next Data Row
The cursor declared above is a forward-only cursor. This means that it is only possible to advance through the rows one by one. It is not possible to jump to a specific position, skip rows or to reverse through the data. As such, there is only one available command that can be used to obtain a row from the cursor. This command is "FETCH NEXT".
FETCH NEXT FROM BillingCursor
If you execute this command from SQL Server Management Studio you will see the first row of data displayed in the results area. Each subsequent execution of the fetch returns the next row until the cursor is exhausted.
Closing a Cursor
Once you have finished working with a cursor it must be closed to free the resources associated with it and to clear any locks that the cursor has created. To close the cursor, execute the following:
Deallocating a Cursor
A closed cursor can be reopened and reused multiple times. This is because the data structures of the cursor are not released when the cursor is closed. To release these structures and destroy the cursor after closing you should deallocate it. To deallocate the sample cursor, execute the following:
Retrieving Cursor Data into Variables
The example above is interesting as it shows the use of a cursor. However, in a real-world situation it would not be particularly useful. Generally, you will wish to fetch the row data into variables that can then be used for further processing. For our example we require five variables for the five columns in the query. Declare the five variables as follows:
DECLARE @JobID UNIQUEIDENTIFIER
DECLARE @ContractNumber INT
DECLARE @Duration DECIMAL(4,2)
DECLARE @EngineerCost MONEY
DECLARE @PartsCost MONEY
To fetch the data from the cursor into the variables, use the INTO clause. This clause is followed by a comma-separated list of the variables to be populated. The first variable will receive the value of the first column in the query, the second from the second column and so on. One variable must be supplied for each of the query's columns.
FETCH NEXT FROM BillingCursor
INTO @JobID, @ContractNumber, @Duration, @EngineerCost, @PartsCost
23 January 2010