Using Cursors

When you execute a SELECT statement, all the rows are returned in one go. This might not always be appropriate. For example, you might want to take some action based on the column values retrieved for a particular row. To do this, you can use a cursor to process rows retrieved from the database one row at a time. A cursor allows you to step through the rows returned by a particular SELECT statement.

You follow these steps when using a cursor:

1. Declare variables to store the column values from the SELECT statement.

2. Declare the cursor, specifying your SELECT statement.

3. Open your cursor.

4. Fetch the rows from your cursor.

5. Close your cursor.

You'll learn the details of these steps in the following sections.

Step 1: Declare Variables to Store the Column Values from the SELECT Statement

These variables must be compatible with the column types for the retrieved rows. For example, you'll want to use an int variable to store the value from an int column, and so on.

The following example declares three variables to store the ProductID, ProductName, and UnitPrice columns from the Products table:

 DECLARE @MyProductID int DECLARE @MyProductName nvarchar(40) DECLARE @MyUnitPrice money 

Step 2: Declare the Cursor

A cursor declaration consists of a name that you assign to the cursor and the SELECT statement that you want to execute. This SELECT statement is not actually run until you open the cursor. You declare your cursor using the DECLARE statement.

The following example declares a cursor named ProductCursor with a SELECT statement that retrieves the ProductID, ProductName, and UnitPrice columns for the first 10 products from the Products table:

 DECLARE ProductCursor CURSOR FOR SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID <= 10 

Step 3: Open the Cursor

Now it's time to open your cursor, which runs the SELECT statement previously defined in the DECLARE statement. You open a cursor using the OPEN statement. The following example opens ProductCursor, and therefore also runs the SELECT statement that retrieves the rows from the Products table:

 OPEN ProductCursor 

Step 4: Fetch the Rows from the Cursor

Now you must read each row from your cursor. To do this, you use the FETCH statement. Your cursor may contain many rows, and therefore a WHILE loop is required is to read each row in turn. To determine when the loop is to end, you can use the @@FETCH_STATUS function. This function returns one of the possible values shown in Table 4.1.

Table 4.1: RETURN VALUES FROM THE @@FETCH_STATUS FUNCTION

VALUE	DESCRIPTION
0	FETCH statement successfully returned a row.
-1	FETCH statement failed or the requested row was outside the result set.
-2	Row fetched was missing.

The following example shows a loop that reads each row from ProductCursor:

 FETCH NEXT FROM ProductCursor INTO @MyProductID, @MyProductname, @MyUnitPrice PRINT '@MyProductID = ' + CONVERT(nvarchar, @MyProductID) PRINT '@MyProductName = ' + CONVERT(nvarchar, @MyProductName) PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice) WHILE @@FETCH_STATUS = 0 BEGIN   FETCH NEXT FROM ProductCursor   INTO @MyProductID, @MyProductname, @MyUnitPrice   PRINT '@MyProductID = ' + CONVERT(nvarchar, @MyProductID)   PRINT '@MyProductName = ' + CONVERT(nvarchar, @MyProductName)   PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice) END 

You'll notice that the condition @@FETCH_STATUS = 0 is used in the WHILE loop to check that the FETCH statement successfully returned a row. When this condition is no longer true, the loop ends.

Tip 

You can get the number of rows stored in a cursor using the @@CURSOR_ROWS function. You'll learn more about functions later in the "Using Functions" section.

Step 5: Close the Cursor

Close your cursor using the CLOSE statement. The following example closes ProductCursor:

 CLOSE ProductCursor 

You should also remove the reference to your cursor using the DEALLOCATE statement. This frees the system resources used by your cursor. The following example removes the reference to ProductCursor using the DEALLOCATE statement:

 DEALLOCATE ProductCursor 

The following section shows a complete example script that you may run using Query Analyzer. This script contains all five steps for using a cursor.

Complete Example: ProductCursor.sql

Listing 4.1 shows the ProductCursor.sql script. You can load this file into Query Analyzer and run it.

Listing 4.1: USING CURSORS

 /*   ProductCursor.sql uses a cursor to display   the ProductID, ProductName, and UnitPrice columns   from the Products table */ USE Northwind -- step 1: declare the variables DECLARE @MyProductID int DECLARE @MyProductName nvarchar(40) DECLARE @MyUnitPrice money -- step 2: declare the cursor DECLARE ProductCursor CURSOR FOR SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID <= 10 -- step 3: open the cursor OPEN ProductCursor -- step 4: fetch the rows from the cursor FETCH NEXT FROM ProductCursor INTO @MyProductID, @MyProductname, @MyUnitPrice PRINT '@MyProductID = ' + CONVERT(nvarchar, @MyProductID) PRINT '@MyProductName = ' + CONVERT(nvarchar, @MyProductName) PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice) WHILE @@FETCH_STATUS = 0 BEGIN   FETCH NEXT FROM ProductCursor   INTO @MyProductID, @MyProductName, @MyUnitPrice   PRINT '@MyProductID = ' + CONVERT(nvarchar, @MyProductID)   PRINT '@MyProductName = ' + CONVERT(nvarchar, @MyProductName)   PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice) END -- step 5: close the cursor CLOSE ProductCursor DEALLOCATE ProductCursor 

The output for the first two rows read by the cursor is as follows:

 @MyProductID = 1 @MyProductName = Chai @MyUnitPrice = 18.00 @MyProductID = 2 @MyProductName = Chang @MyUnitPrice = 19.00 …