This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Cursors in SQL Declaring and Using Cursors in SQL (this blog) Fetching Records with an SQL Cursor SQL Cursor Options Updating Records Using a Cursor This blog is part of our complete SQL tutorial; Wise Owl also run introductory and advanced SQL training courses.

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.

Cursors in SQL
Declaring and Using Cursors in SQL (this blog)
Fetching Records with an SQL Cursor
SQL Cursor Options
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

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:

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.

Parts of this blog
Cursors in SQL Declaring and Using Cursors in SQL (this blog) Fetching Records with an SQL Cursor SQL Cursor Options Updating Records Using a Cursor

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Declaring and Using Cursors in SQL

The Syntax of a Cursor Declaration

Opening and Closing Cursors

Making a Cursor Step Through a Set of Records

What's Next?

Head office

London

Manchester