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:
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 makes a good tool for general debugging and playing around with stored procedures:
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.
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/