Stored Procedures (Part 4) - Cursors

Hi everyone! Today's blog post is a continuation of Stored Procedures (Part 1), Stored Procedures (Part 2) and Stored Procedures (Part 3) which I would strongly recommend reading before reading this one.

Today, we will be discussing database cursors and their uses within T-SQL. I have come across them briefly when working on our main project, primarily within triggers. We have used them to apply custom IDs to our multiple record types to allow for multiple records being added simultaneously where you want it on a row by row basis but not on a set basis. In my own mini-project portfolio, I have also demonstrated my knowledge by creating something similar. 

What are Cursors? 

Cursors are used when you want to iterate through every single row in a table to do something to it (and work in a similar way to loops, which you may already be aware of in other programming languages). Usually, you would want to process sets of data as it is much faster however there are occasions where you can't, and therefore have to use a cursor. 

Cursors are split into multiple parts: 

Firstly, you declare the cursor. You give it a name and define the SELECT statement that the cursor will run through:

Next, you open the cursor which causes the SELECT statement to be executed and the result set is stored within the database memory. Without this statement, the cursor will not work at all:

The FETCH statement is then used to retrieve each row of data from the cursor. Not only should this be included at the beginning of the cursor, but it should also be used just before the END statement. Otherwise, the operation will only be performed on the first data row continuously in a recursive loop and not the rest of the rows.

Usually, a WHILE loop is used to iterate through the rows and this is where the conditions are placed for when the loop should continue to loop (exactly the same kind of principles as a while loop in any other kind of programming language). In this instance, the WHILE loop is being iterated while the @@FETCH_STATUS = 0:

The function @@FETCH_STATUS will return a value depending on the next row in the result set:

•  0 - The FETCH statement was successful and has returned a new row, 
• -1 - THE FETCH statement failed or the row that was attempted to be retrieved went beyond the result set declared in the initial SELECT statement. 
• -2 - The row fetched is missing (or may have been deleted) from the result set. 

Next, you will need to encapsulate any operations that need to be applied to each individual row in the result set within a BEGIN and END statement:

After the END statement, make sure that you close the cursor:

After closing the cursor, by habit you should deallocate the cursor as it reclaims any memory space used up. This is not necessary within Stored Procedures as when the Stored Procedure exits, it automatically closes and deallocates the cursor. However within Triggers and Functions, it may not. So just to be on the safe side, I would always make sure that I manually close and deallocate the cursor, like below:

And that is how to declare a default (FAST_FORWARD) cursor.

What Types of Cursor Can I Declare? 

Within SQL Server, there are 4 different cursors that you can declare and use: 

FAST_FORWARD (or FORWARD_ONLY or READ_ONLY) Cursor 

This is the standard default cursor that you can create (as shown in the example above). It is the fastest of all cursors that you can declare however it will only allow you to move through the result set forwards one row at a time. Scrolling is not allowed.

STATIC Cursor 

STATIC cursors push the result set into a temporary table in the database and all FETCH statements will be from that temporary table. Scrolling is allowed but no modifications can be made, it is read only. 

KEYSET Cursor 

KEYSET cursors places the unique keys of each row in the result set into a temporary table. Scrolling is allowed and modifications can be made, however any inserts into underlying tables will not be available to the KEYSET cursor. 

DYNAMIC Cursor 

DYNAMIC cursors are the most costly to use (in terms of memory allocation and speed). The cursor will have all modifications made available to it. 

Different FETCH Statements

Not only can your cursor be customised by one of the 4 types listed above, different FETCH options can also be declared. In the example above, I used FETCH NEXT which is possibly the most commonly used option. However (as knowledge to myself and other readers of this blog) I am going to list some of the others and their purposes: 

FETCH FIRST

FETCH the first row in the result set.

FETCH LAST

FETCH the last row in the result set.

FETCH NEXT

FETCH the next row in the result set dependant on the current position of the cursor in the result set.

FETCH PRIOR

FETCH the previous row in the result set dependant on the current position of the cursor in the result set.

FETCH ABSOLUTE n

FETCH the nth row from the beginning of the result set. Note that you can't use FETCH ABSOLUTE n when using a DYNAMIC cursor. 

FETCH RELATIVE n

FETCH the nth row forward dependant on the current position of the cursor in the result set. 

The Downside To Using Cursors 

In performance terms, cursors should be avoided as they can be incredibly slow. However there will be times where an action can only be completed by using a cursor. If this is the case, you may need to use other query optimising techniques in order to increase performance and data return speeds. Query performance optimising will form a later blog post as it is essential to the success of a database application, particularly from the user's point of view. 

And As An End Note... 

The 4 Stored Procedure blogs that I have written should cover the majority of what is required both in the work place and also for the relevant Microsoft exam. My next blog posts will focus on User-Defined Functions and how they are used within T-SQL. Keep your eyes peeled guys! If you have any questions or feedback, please don't hesitate to comment below.