Ashok Kumar SQL Development and DBA Adminstrations Techi Blog

Cursors: An Overview

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
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

 PRINT @AuthorID

 FETCH NEXT FROM c1
 INTO @AuthorID

END

CLOSE c1
DEALLOCATE 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
FROM authors
FOR UPDATE OF au_lname

You can code your UPDATE statement to update the current row in the cursor like this

UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1

        ---------------Forward cursor's declaration----------

DECLARE Col CURSOR FAST_FORWARD

FOR

--Distinct state name will be iterate in cursor and will become column name

SELECT DISTINCT State as [column] FROM SalesSummaryOfRegions Order By State

--Opening the cursor

OPEN Col

--fetch first record (state name) from cursor

FETCH Col INTO @strColumnList

--while loop iterates number of records times

WHILE @@FETCH_STATUS=0

BEGIN

--assign state name in query variable

SET @strSQL=@strSQL+@strColumnList+', '

--after assigning state, fetching next records

FETCH Col INTO @strColumnList

END

--after finishining iteration closing and deallocating cursor from memory

CLOSE Col

DEALLOCATE Col

--now creating SELECT statement from state list assigned to @strSQL variable.

SET @strSQL=LEFT(@strSQL,LEN(@strSQL)-1)

SET @strSQL='SELECT Item, ' + @strSQL 

+ ' FROM SalesSummaryOfRegions PIVOT (SUM(TotalSales) for State IN ('

+ @strSQL + ')) AS piv'

--execute SELECT statement created in @strSQL

EXEC sp_executeSQL @strSQL