Monday, August 18, 2008

Using a T-SQL Cursor in SQL Server

I can never seem to remember the syntax for a cursor in T-SQL so I decided to add it to my blog. If you are not familiar with a cursor, it is essentially a way to loop through selected rows in a SQL database.
Please use caution with cursors. They are very poor performers and often other options make more sense for performance reasons. However, there are times when it makes sense to use cursors. It can be difficult for programmers to think like DBAs sometimes because programming is typically a step-by-step logical progression of thought, but SQL databases should be thought of in terms of sets. Cursors are in a gray area that kind of abuses SQL a little bit because that is not what databases are optimized for.
There is my two cents. Assuming you need to use cursors here is how they work. Basically a cursor takes a query, and copies each column in that select statement into T-SQL variables. You can then do whatever you want with these variables. In our case we will send email. Once you are done with that row you get the next row.
In general you want to use the where clause to limit the rows that the cursor has to go through. For example, do NOT do something like (select * from Person) and then in the cursor use an if statement to ignore all records that don't meet your criteria. This is best done in a where clause.
Let's assume you have a query like the following:

Select fname, lname, email from Person where city = 'Phoenix'
order by fname


Now you want to send an email to each of these people. Here is what you do:

  1. Declare variables for each of the columns you are select Declare @FName as nvarchar(50)
    Declare @LName as nvarchar(100)
    Declare @Email as nvarchar(255)
  2. Declare the Cursor Declare PersonCursor CURSOR FAST_FORWARD FOR
  3. Now your we use the select statement (the one you want to loop through) Select fname, lname, email from Person where city = 'Phoenix' order by fname
  4. Open the cursor and copy the columns into the T-SQL variables. Be sure to order the select statement columns and the columns below in the same order since the order determines how the values are mapped.
    OPEN PersonCursor FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
  5. Now that we have the data for the first row, let's start the WHILE loop. Notice, that we get the first row BEFORE the WHILE loop is started so that the loop condition will be satisfied to start with. To control when the WHILE loop will exit we use the @@FETCH_STATUS built-in T-SQL variable. Notice that at the end of the WHILE loop we use exact same FETCH lines as we did to get the first row of data.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- do row specific stuff here
       print 'FName: ' + @FName print 'LName: ' + @LName print 'Email: ' + @Email
       FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
    END
  6. The most important lines are the next two lines. These are the lines that free the resources. If you don't do this you have what they call a memory leak. It can eat up valuable server memory and possibly eventually take down your server if you don't clean up after using resources.
    CLOSE PersonCursor
    DEALLOCATE PersonCursor
Here is the completed example in one big block.
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)
Declare @Email as nvarchar(255)

Declare PersonCursor CURSOR FAST_FORWARD FOR 
Select fname, lname, email from Person where city = 'Phoenix' order by fname
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
 
WHILE @@FETCH_STATUS = 0
BEGIN
   -- do row specific stuff here
   print 'FName: ' + @FName print 'LName: ' + @LName print 'Email: ' + @Email
   FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email

END 
CLOSE PersonCursor
DEALLOCATE PersonCursor

42 comments:

LuKeNuKuM said...

great post, thanks very much - got me out of a sticky spot! :) keep up the good work!

Brent V said...

lukenukum,

Thank you so much for the feedback. I really appreciate it. I'm so glad you found this posting useful.

Anonymous said...

Truly awesome! Made a complex subject easy to understand.

Brent V said...

DB,

I'm so glad it was helpful. I use it when I forget. :)

Thank you for your feedback.

Anonymous said...

Superb. Very precise and clear.
Thanks very much.

Anonymous said...

You should write books!!
This post is so helpful!

Brent V said...

Anonymous,

I am happy you found this helpful. I always forget the syntax and end up copying it from here. Thank you so much for the kind words. It means a lot to me.

Thx,

Brent

Anonymous said...

This is the most succint information i must have found till date on Cursors.. Good job ....

Brent V said...

Snehal Sahni,

Thank you so much the nice words. I greatly appreciate it.

Thank you,

Brent

Anonymous said...

Really informative and clean...
you rock!!

Brent V said...

Anonymous,

Thank you for the kind words. It is greatly appreciated.

Thank you,

Brent

Sean said...

Thanks for this. Well explained.

Brent V said...

Sean,

Thanks for the kudos. I appreciate it.

Brent

Anonymous said...

Most of us have heard cursors should be avoided if possible, and that there are often better methods to accomplish the same thing a cursor would do. I would love to hear an opinion about when cursors are the best option.

Brent V said...

Michael D,

That is a great question. Cursors can be avoided almost always, and they should. In most cases it is just a programmatic vs. set theory kind of paradigm shift that is needed. However, there are like you said always exceptions to this heuristic.

An excellent example is when you need to call a stored procedure such as sending mail on a selection of records. There is no real good way to do this except with a cursor.

Another example might be when performance is not critical, but complex manipulation is required. For pre-processing of data for a one time or scheduled data import or export may be a good time for cursors (if they are needed).

In general, anytime the cursor is not the bottleneck. By that I mean that whatever you are doing for each record in the cursor actually takes a long time for each record.

There have only been a hand full of time in the 13+ years that I have been programming.

I hope this helps.

PHenry said...

Thank you very much for this informamtion. I liked it so much I even wrote my own blog about it (cause I forget as well). Thank you.

http://www.pchenry.com/Home/tabid/36/EntryID/126/Default.aspx

Brent V said...

PHenry,

Thank you for leaving feedback and referencing my blog in your blog. That is pretty cool in my book. Thank you. Thanks for the feedback as well. And, yes, I still use this page as well as I forget also. :)

Take care,

Brent

omkar dande said...

Hi,
This is a great post and very simple to understand.
Made my life easy.

Brent V said...

omkar dande,

Thank for the kind feedback. I am so glad it helped. Thanks!

Brent

apronless said...

thanks for the posting. very helpful for a beginner.

FrodeM said...

Excellent example Brent. Solved a problem for me. Thanks :)

Brent V said...

FrodeM,

Thank you very much for the kind words. I'm so glad it helped.

Thx,

Brent

Brent V said...

Apronless,

I'm glad you found it helpful. Thank you.

Brent

JL said...

I have been trying to get my head around Cursors all day and this made it much easier to uderstand, Microsoft website take note!! Many thanks

Brent V said...

JL,

Thank you very much for the very kind words. It is greatly appreciated. I found the same thing to be true with other articles on cursors. They were never quite clear enough for me.

I'm glad you found this helpful.

Brent

Craig Glenn said...

Brent,

Thank you for this post. I found it well written and VERY helpful. I was quite frustrated with "HELP" so I did a search for what I wanted to do and found your post.

I look forward to more visits to your blog!

Craig Glenn

Brent V said...

Hi Craig,

Thank you very much for the very nice Kudos. I really appreciate your feedback.

Thank you,

Brent

Unknown said...

Most of the time if you really need a cursor (like the sendmail example) you can do it faster with a loop like this:

SET @@NextID = NULL

SELECT TOP 1 @@NextID = IdField
FROM MyTable
WHERE ...
ORDER BY IdField ASC

WHILE NOT (@@NextID IS NULL)
BEGIN
/* Do your thing */
EXEC SendMail @@NextID

SET @@LastID = @@NextID
SET @@NextID = NULL

SELECT TOP 1 @@NextID = IdField
FROM MyTable
WHERE ...
AND IdField > @@LastID
ORDER BY IdField ASC
END

Test this versus a cursor on a dataset of any consequence and you'll see serious performance improvements.

Brent V said...

Hi Tony,

I agree Cursors do not usually yield the best performance. You solution makes a good point. It really depends on what you are trying to do. The right choice for implementation can make all the difference. Personally, I try to avoid cursors where possible, but this is not always possible.

Thanks for pointing out an alternative.

Thx for the feedback. Always appreciated.

Brent

Saurabh said...

Tony & Brent V I tried both way but surprisingly Tony's option return only 2 records & then failed while Cursor works fine here is the code I used for Northwind Employees Table.
1st option
Use Northwind
Go
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)
Declare @EmpId as nvarchar(255)
Declare EmpCursor CURSOR FAST_FORWARD FOR
Select EmployeeId,FirstName,LastName From Employees
order by FirstName

OPEN EmpCursor
FETCH NEXT FROM EmpCursor
INTO @EmpId,@FName, @LName

WHILE @@FETCH_STATUS = 0
BEGIN
print 'FName: ' + @FName
print 'LName: ' + @LName
print 'EmpId: ' + @EmpId

FETCH NEXT FROM EmpCursor
INTO @EmpId, @FName, @LName
END

CLOSE EmpCursor
DEALLOCATE EmpCursor

2nd option
Use Northwind
Go
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)
Declare @EmpId as nvarchar(255)
Declare @LastId as nvarchar(255)

Set @EmpId=null
Set @FName=null
Set @LName=null
Set @LastId=null
Select Top 1 @EmpId = EmployeeId, @FName=FirstName,@LName=LastName from Employees order by FirstName
While (@EmpId is Not Null)
BEGIN
print 'FName: ' + @FName
print 'LName: ' + @LName
print 'EmpId: ' + @EmpId
--print 'LastId: ' + @LastId
Set @LastId=@EmpId
Set @EmpId=null
Select Top 1 @EmpId = EmployeeId, @FName=FirstName,@LName=LastName from Employees Where EmployeeId>@LastId order by FirstName

END

Brian said...

Thank you for taking the time to put this together.... It was the best explanation I found.

Unknown said...

@saurabh

You have to use the same field for the ORDER BY and for the @LastID vaiable. You're sorting by Name but looking for the next larger ID - that will give random results. Sort by EmployeeID and it will work.

Jef Claes said...

Thanks, I always tend to forget something when using cursors.

Anonymous said...

I have tested two options that are given above with some data and have found the cursor is twice faster then the 2nd option. Curosr query time on 3146 rows was 3mins 15 sec while option 2 on same number of rows was 7 mins 06 sec.

Unknown said...

I am looking to create a Query that will produce results from a table based on the variables sent to it. Google hasn't been very helpful, and this blog seems to be on a path in the right direction...

Basically, I want to be able to use the same query, but populate just the 4 variables, so no one has to edit the actual query itself.
I would like to set the variables, Show the 'before', make the change, then show the 'After' to verify it works, then try again on the Next DataBase in the Server.


--The example, which doesn't work, because select can't seem to
--pull variables the way this is written:


DECLARE @DBName VARCHAR(50)
DECLARE @TableName VARCHAR(50)
DECLARE @ColumnName VARCHAR(50)
DECLARE @BeforeString VARCHAR(50)
DECLARE @AfterString VARCHAR(50)
DECLARE @RSLT VARCHAR(100)

Set @DBName = 'MyAddyBook' -- The first database to check
Set @TableName = 'AllUsers' -- The Table
Set @ColumnName = 'DEFAULT_EMAIL' -- The Column in the Table
Set @BeforeString = '123456[at]test.com' -- The item to look for in the Row of the Table
Set @AfterString = '654321[at]test.com' -- The updated data


Use @DBName
Select @ColumnName from @TableName AS 'Before Change'
Where @ColumnName like '@BeforeString'

Update @TableName
Set @ColumnName = Replace(@ColumnName, @BeforeString, @AfterString) -- this is used because there may be other comma separated information in this column.

Use @DBName
Select @ColumnName from @TableName AS 'After Change'
Where @ColumnName like '@AfterString' and date(edit_date)>=date( NOW())

---------------------------------
Results:

Before Change
------------------------
123456[at]test.com
1 Row(s) Returned
**
Query Executed Successfully.
**
After Change
------------------------
654321[at]test.com
1 Row(s) Returned



Secondly:
Once I get this part working, I would like to have this script loop through multiple DBs on the same Server. I am guessing that there would have to be a cursor or loop of some sort to specify what the 'Use [DBNAME]' changes to...

Any help would be appreciated on these topics. Thank you so much. Google hasn't been very helpful explaining why variables can't pass in a select query.

Sri said...

Very useful. Good work

Anonymous said...

GREAT post; very, very clear explanation.

Unknown said...

Thanks very much! Your post helped me troubleshoot a cursor I've been breaking my head over for a day now.

Anonymous said...

Thanks a lot saved my day.

Anonymous said...

Awasome, thanx for explanation. U rock

Raja Sekhar S said...

Great Post.... It's Very Useful... Especially For the Beginners.....
Nice Work.. Keep Going......

Manvendra said...

Hi Brent,
This is a very nice post. I've implemented it for my stored procedure but it is showing
"Column name or number of supplied values does not match table definition."
Any ideas?