Cursors

The definition of the word cursor can actually be a little confusing. This is because there are several meanings that have similar context. Before going any further, I'd like to clarify our use of this word. If asked, most computer users would likely tell you that the little blinking bar in a text editor is a cursor. It's a placeholder or position pointer. You could reason that after a SQL query is executed, the cursor would be a pointer to the current record. As much as this makes sense, this isn't the definition of a cursor in the SQL world. In this context, the word cursor is an acronym for CURrent Set Of Rows. A cursor is a set of records returned from a query.

Unfortunately, it can get a little more confusing than that. It is true that when a SELECT statement is executed, the database engine returns a cursor — or set of records — held in memory or streamed across a network connection. This type of cursor is the basis for programming objects such as recordsets and datasets. In Transact-SQL, when you declare a cursor-type variable, a result set is read into the memory managed by this object for the purpose of iterating through each record. I know, this actually takes us back to the original definition of a cursor as a single record pointer. The fact is that the cursor represents the entire set of records and supports navigation, one record at a time.

Rowset Versus Cursor Operations

Probably one of the greatest challenges for programmers learning to use SQL is the different approach usually taken to handling data. In procedural and object-oriented programming, developers typically work with objects one at a time. Groups of objects are organized into collections, structures, or arrays that support looping and enumeration. So far, you've seen that SQL Server returns sets of rows, all at once, and is optimized to support this paradigm rather than one record at a time. SQL Server performs best when using native set-based operations. There may be times when you will need to loop through a set of individual records, but be forewarned that cursor operations are typically the worst performing operations that occur in T-SQL. If there is a set-based solution than can provide the same functionality as the cursor, choose the set-based solution.

T-SQL cursors' default behavior is forward-only and updatable, unlike their counterparts used in other programming languages, which typically default to forward-only read-only. This makes the T-SQL cursor even more expensive as far as performance is concerned. When a cursor is updatable it means that the cursor maintains a link to the underlying data that it is built from. If the underlying data changes, so will the contents of the cursor. To prevent this behavior the cursor can be declared with an INSENSITIVE option that makes it read-only and decreases the amount of resources needed to maintain it. Another method of mitigating the cost of a cursor is to mark only specific columns in the cursor as updatable rather than the default of all the columns. If you must use a cursor, keep this in mind and use the least expensive cursor possible. The examples used in this chapter follow these performance guidelines.

Creating and Navigating a Cursor

The first order of business is to declare a cursor-type variable. Because this is a special type of non-scalar object, the variable name isn't prefixed with an at symbol (@). The cursor variable can be declared and defined on the same line with the SELECT statement used to populate the cursor:

 DECLARE curProduct INSENSITIVE CURSOR FOR SELECT ProductID, Name FROM Product 

To create a cursor that allows updates only to and from the Name column of the Product table, declare the cursor like this:

 DECLARE curProduct CURSOR FOR SELECT ProductID, Name FROM Product  FOR UPDATE OF Name 

The cursor isn't actually populated until it is opened. Executing the OPEN command loads the cursor structure and data into memory:

 OPEN curProduct 

At this point, the record pointer is positioned before the first row. The FETCH NEXT command navigates the pointer to the next record and returns a comma-delimited list of column values. In this case, the pointer is moved to the first row. Individual variables can be used to capture the values of the current row's column values:

 DECLARE @ProdID Int DECLARE @ProdName VarChar(100) FETCH NEXT FROM curProduct INTO @ProdID, @ProdName 

After FETCH NEXT is executed, one of two things will happen: the record pointer will either be positioned on a valid record or it will navigate beyond the last row. The state of the pointer can be determined using the global variable @@Fetch_Status. On a valid row, it returns 0, otherwise it returns –1 or –2. It returns –1 if there is no next row to fetch. If a –2 is returned it means that the next row was deleted in the underlying table when using an updatable cursor. Using this variable, create a simple loop, navigating to the next record as long as @@Fetch_Status is equal to 0:

 WHILE @@Fetch_Status = 0 BEGIN PRINT @ProdName FETCH NEXT FROM curProduct INTO @ProdID, @ProdName END 

In this example, you're simply printing one of the variable values to the query results window. In production, you could use conditional statements to decide whether to perform related operations, such as inserting or deleting records. The real power of using cursors is in using them to conditionally call stored procedures. This way you can use conditional logic to call different procedures under different conditions, and then a stored procedure can perform practically any combination of operations.

Finally, after navigating past the last record, it's necessary to do some clean up. Use the CLOSE command to close the cursor and then use the DEALLOCATE command to recover the memory used by the cursor:

 CLOSE curProduct DEALLOCATE curProduct 

Putting it all together, here's the entire script:

 DECLARE curProduct INSENSITIVE CURSOR  FOR SELECT ProductID, Name FROM Product DECLARE @ProdID Int DECLARE @ProdName VarChar(100) OPEN curProduct FETCH NEXT FROM curProduct INTO @ProdID, @ProdName WHILE @@Fetch_Status = 0 BEGIN PRINT @ProdName FETCH NEXT FROM curProduct INTO @ProdID, @ProdName END CLOSE curProduct DEALLOCATE curProduct 

A list of product names is displayed in the query results pane, as shown in Figure 10-18.

Figure 10-18: