Click here to Skip to main content
15,867,835 members
Articles / Database Development

What is a Database Cursor?

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
7 Mar 2022CPOL3 min read 7.3K   2  
Learn about a database cursor with an example and when to use it in a SP
After reading this article, you will understand what a database cursor is, see an example, and understand when to use it 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.

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 an operation on a row by row basis. This is where cursors come into 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 even be 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.

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:

SQL
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.

SQL
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:

SQL
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 makes 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 appreciation 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 appreciation for what they were and to understand the concept.

This article was originally posted at https://www.essentialsql.com/database-cursor

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --