Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!

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,
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,
    FROM  Person.Person
    OPEN @personCursor
FETCH NEXT FROM @personCursor INTO @businessEntityID, 
   PRINT cast(@BusinessEntityID as VARCHAR (50)) 
         + ' - ' + @firstName
         + ' ' + @lastName;
   FETCH NEXT FROM @personCursor INTO @businessEntityID, 
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:

Database Cursor Example Messages
Database Cursor Example Output

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.

6 responses to “What is a Database Cursor?”
  1. Vapshe tushunmadim

  2. Xanpoll

    change ” OPEN @personCursor” to “OPEN personCursor”,
    you know that’ll correct

    1. Kris Wenzel

      Hi Xanpoll, either form works — try it!

  3. Ashraf

    Really, thanks for this helpful information.

  4. Ramandeep kaur

    very helpful

  5. Saeed Talib

    Hi everybody,

    This is really helpful article.
    what i got it:

    Triggers are Table events which gets fire when corresponding event occurs (Record inserted etc).Triggers are applied on table and we can use triggers to maintain the integrity of database. by using trigger we can set audit log of all the insertion deletion and update happened for specific table.

    In Cursor by it means its an iterating process of resultant record. if we run a query and we want some checks on retrieved records or wants to loop through each row of resultant query we will use Cursors.

    Another terminology is stored procedures. by names it is a procedure stored on database . working is similar to simple queries except it takes arguments can return values and also can be stored on database for the later use.
    To execute the procedures USE EXECUTE command followed by procedure name followed by parameters if it accepts some arguments .

    Hope so these three major concepts could hit your mind.

    Muhammad Saeed

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SQL Server