What is a Database Cursor?

After reading this article you will understand what a database cursor is, see an example, and understand when to use them in a stored procedure.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters.  You can get started using these free tools using my guide Getting Started Using SQL Server.

SQL is a set-based language, meaning operations are completed on all or rows of the result.  However, there are times, when you want to do operation on a row by row basis.  This is where cursors come in to play.

What is a Database Cursor?

A database cursor can be thought of as a pointer to a specific row within a query result.  The pointer can be moved from one row to the next.  Depending on the type of cursor, you may be even able to move it to the previous row.

Think of it this way:  a SQL result is like a bag, you get to hold a whole bunch of rows at once, but not any of them individually; whereas, a cursor is like a pair of tweezers.  With it, you can reach into the bag and grab a row, and then move onto the next.

Want to Learn More?  Read our popular post Stored Procedures: The Ultimate Guide

Types of Cursors

The type of cursors you can define are broken in two main categories: scrolling capabilities and ability to detect changes made to the database.

Let’s first talk about scrolling capabilities.  Cursors can be defined with two main scrolling capabilities, FORWARD_ONLY or SCROLL.

• FORWARD_ONLY – The cursor starts on the first row and end on the last. The cursor can only move to the next row in the result.
• SCROLL – the cursor can use operations, such as FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE to navigate the results.

When we talk about data sensitivity we mean what happens when the same row is changed by someone else?  Is that change seen in the result of the cursor?

• STATIC – any changes made aren’t reflected in the cursor’s results. Also, any change made to the cursor’s data, aren’t permanent.  They aren’t stored to the underlying database tables.
• KEYSET – A keyset cursor can see changes made to rows that are originally in to cursor, since these rows unique identifiers (keys) are used to fetch rows during cursor operations. A keyset cursor cannot see rows added to the table.
• DYNAMIC – changes made are reflected in the cursors. Likewise, changes made within the cursor are made to the underlying database.

Why Database Cursors are Used

The reason you may need to use a database cursor is that you need to perform actions on individual rows.

For example, consider this update statement:

UPDATE esqlSalesPerson
SET    City = 'Saline'
WHERE  SalesPersonID < 10031

It updates every row in the table esqlSalesPerson where the SalesPersonID is less than 10031.  If, during the update operation, there is an error, then no rows are updated.  The entire update is treated as a transaction.

Now by using a cursor, we can iterate or move from one row to the next and updating rows as we go.  If we encounter an error, try something else, or skip the operation.  The difference is, that when you use cursors, you can act on each row.

Also, if the cursor is defined as SCROLLABLE we can even move back to the previous row.

Example Database Cursors in SQL

The purpose of this example isn’t to go full detail on how to build a cursor, we’ll do that in a later article, rather, it’s to show you an example so you’ll be able to recognize them.

Consider the following select statement.  We’ll use this for the basis of our cursor.

SELECT BusinessEntityID,
       FirstName,
       LastName
FROM   Person.Person

Here are the general steps we do to set up a cursor:

• Declare Variables
• Declare Cursor
• Fetch values into variables
• Test Status and Loop
• Close Cursor
• Deallocate Cursor

Here is the code for the cursor:

DECLARE @businessEntityID as INT;
DECLARE @firstName as NVARCHAR(50),
        @lastName as NVARCHAR(50);
DECLARE @personCursor as CURSOR;

SET @personCursor = CURSOR FOR
    SELECT BusinessEntityID,
           FirstName,
           LastName
    FROM  Person.Person
    OPEN @personCursor
FETCH NEXT FROM @personCursor INTO @businessEntityID, 
                                   @firstName,
                                   @lastName
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT cast(@BusinessEntityID as VARCHAR (50)) 
         + ' - ' + @firstName
         + ' ' + @lastName;
   FETCH NEXT FROM @personCursor INTO @businessEntityID, 
                                      @firstName, 
                                      @lastName
END
CLOSE @personCursor;
DEALLOCATE @personCursor;

Notice that we used the PRINT statement.  This is a handy statement.  It will send output to the SQL Server Management Studio Message Window!  This make is a good tool for general debugging and playing around with stored procedures:

I hope you now have a better appreciate of cursors.  In future articles I go into greater depth on how to define cursors and program for them, but until then, I thought it was important for you to have an appreciate for what the were and understand the concept.