Cursors: An Overview
By Bill Graziano
on 30 August 2000
| 2 Comments
| Tags: Application Design, Queries
We'll talk about the basics of cursors. These let you move through rows one at a time and perform processing on each row. (This article has been updated through SQL Server 2005.)
SQL Server is very good
at handling sets of data. For example, you can use a single
UPDATE statement to update many rows of data. There are
times when you want to loop through a series of rows
a perform processing for each row. In this case you can
use a cursor.
Please note that cursors are the
SLOWEST way to access data inside SQL Server. The should
only be used when you truly need to access one row at a time. The only
reason I can think of for that is to call a stored procedure on each row.
In the Cursor Performance article I discovered that cursors are over
thirty times slower than set based alternatives.
The basic syntax of a cursor is:
DECLARE @AuthorID char(11)
DECLARE c1 CURSOR READ_ONLY
FETCH NEXT FROM c1
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM c1
The DECLARE CURSOR statement defines the SELECT statement
that forms the basis of the cursor. You can do just about
anything here that you can do in a SELECT statement. The
OPEN statement statement executes the SELECT statement and
populates the result set. The FETCH statement returns a
row from the result set into the variable. You can select
multiple columns and return them into multiple variables.
The variable @@FETCH_STATUS is used to determine if there
are any more rows. It will contain 0 as long as there are
more rows. We use a WHILE loop to move through each row
of the result set.
The READ_ONLY clause is important in the code sample
above. That dramatically improves the performance
of the cursor.
In this example, I just print the contents of the variable.
You can execute any type of statement you wish here. In
a recent script I wrote I used a cursor to move through
the rows in a table and call a stored procedure for each
row passing it the primary key. Given that cursors are
not very fast and calling a stored procedure for each row
in a table is also very slow, my script was a resource hog.
However, the stored procedure I was calling was written
by the software vendor and was a very easy solution to my
problem. In this case, I might have something like this:
EXEC spUpdateAuthor (@AuthorID)
instead of my Print statement. The CLOSE statement releases
the row set and the DEALLOCATE statement releases the
resources associated with a cursor.
If you are going to update the rows as you go through
them, you can use the UPDATE clause when you declare a cursor. You'll
also have to remove the READ_ONLY clause from above.
DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FOR UPDATE OF au_lname
You can code your UPDATE statement to update the current row in the
cursor like this
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1
That covers the basics of cursors. You can check Books Online
for more detailed information.