Using Cursors in SQL Server
Part two of a five-part series of blogs

Cursors allow you to step through a set of data one record at a time. They’re not the quickest tool in SQL Server’s box, but they have their uses and this blog explains how they work.

  1. Cursors in SQL
  2. Declaring and Using Cursors in SQL (this blog)
  3. Fetching Records with an SQL Cursor
  4. SQL Cursor Options
  5. Updating Records Using a Cursor

This blog is part of our complete SQL tutorial; Wise Owl also run introductory and advanced SQL training courses.

Posted by Andrew Gould on 26 March 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Declaring and Using Cursors in SQL

In order to use a cursor you must first declare it. When you do this you can specify, amongst other things, the type of cursor you are going to use and which set of records it is going to process.

The Syntax of a Cursor Declaration

To use the most basic, default cursor you will need to declare it, give it a name and say which set of records it will process.  The two lines of code necessary to do this are shown below:

--Declare a cursor and give it a name

DECLARE FilmCursor CURSOR

--Now say which set of records the cursor will process

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

You can use any standard SELECT statement when you specify the set of records that you want your cursor to process.

Opening and Closing Cursors

Once you've declared a cursor, in order to use it you need to open it.  The code to do this is fairly straightforward!

--Open the cursor and load the set of records specified

OPEN FilmCursor

Opening the cursor also executes the SELECT statement and loads the set of records specified ready for the cursor to begin processing.

When you've finished with whatever task you're using the cursor for you should close it and remove any references to it.  The two lines required to do this are shown below:

--Tidy up by first closing the cursor,

CLOSE FilmCursor

--then removing any references to it

DEALLOCATE FilmCursor

The full set of code required to declare, open and close a simple cursor is shown below:

DECLARE FilmCursor CURSOR

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

OPEN FilmCursor

--DO SOMETHING USEFUL HERE

CLOSE FilmCursor

DEALLOCATE FilmCursor

Making a Cursor Step Through a Set of Records

Of course, simply opening and then closing a cursor doesn't perform any sort of useful task; it's what you do in between that determines what the cursor does. As a minimum you would want the cursor to move through the set of records until it reaches the end.  In order to do this you would first need to move the cursor to the first record:

--Move the cursor to the first record

FETCH NEXT FROM FilmCursor

After this you need to tell the cursor to keep moving until it reaches the end of the set of records. You can achieve this using a WHILE loop which checks the value of a global variable called @@FETCH_STATUS. The code to do this is show below:

--Continue moving to the next record until there are no more

WHILE @@FETCH_STATUS = 0

FETCH NEXT FROM FilmCursor

The full procedure to use a cursor to step through a set of records is shown below:

DECLARE FilmCursor CURSOR

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

OPEN FilmCursor

FETCH NEXT FROM FilmCursor

WHILE @@FETCH_STATUS = 0

FETCH NEXT FROM FilmCursor

CLOSE FilmCursor

DEALLOCATE FilmCursor

The results of executing this code is shown in the diagram below:

Results of cursor

Each record is returned as a separate result and takes significantly longer than the equivalent set-based operation.

 

What's Next?

Now that we've looked at the basic syntax required to create a simple cursor the next part of this series will investigate the FETCH statement in more detail.

This blog has 0 threads Add post