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:

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

    ReplyDelete
  2. lukenukum,

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

    ReplyDelete
  3. Truly awesome! Made a complex subject easy to understand.

    ReplyDelete
  4. DB,

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

    Thank you for your feedback.

    ReplyDelete
  5. Superb. Very precise and clear.
    Thanks very much.

    ReplyDelete
  6. You should write books!!
    This post is so helpful!

    ReplyDelete
  7. 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

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

    ReplyDelete
  9. Snehal Sahni,

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

    Thank you,

    Brent

    ReplyDelete
  10. Really informative and clean...
    you rock!!

    ReplyDelete
  11. Anonymous,

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

    Thank you,

    Brent

    ReplyDelete
  12. Thanks for this. Well explained.

    ReplyDelete
  13. Sean,

    Thanks for the kudos. I appreciate it.

    Brent

    ReplyDelete
  14. 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.

    ReplyDelete
  15. 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.

    ReplyDelete
  16. 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

    ReplyDelete
  17. 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

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

    ReplyDelete
  19. omkar dande,

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

    Brent

    ReplyDelete
  20. thanks for the posting. very helpful for a beginner.

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

    ReplyDelete
  22. FrodeM,

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

    Thx,

    Brent

    ReplyDelete
  23. Apronless,

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

    Brent

    ReplyDelete
  24. 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

    ReplyDelete
  25. 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

    ReplyDelete
  26. 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

    ReplyDelete
  27. Hi Craig,

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

    Thank you,

    Brent

    ReplyDelete
  28. 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.

    ReplyDelete
  29. 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

    ReplyDelete
  30. 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

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

    ReplyDelete
  32. @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.

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

    ReplyDelete
  34. 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.

    ReplyDelete
  35. 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.

    ReplyDelete
  36. GREAT post; very, very clear explanation.

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

    ReplyDelete
  38. Thanks a lot saved my day.

    ReplyDelete
  39. Awasome, thanx for explanation. U rock

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

    ReplyDelete
  41. 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?

    ReplyDelete