Thursday, March 01, 2001 ..:: Home ::.. Register  Login
   TechTidBits (Blog)  

SQL Server t-sql Cursors for the forgetful developer

Apr 7

Written by:
Tuesday, April 07, 2009 7:45 PM  RssIcon

I can never remember the t-sql syntax for cursors, so here's my blog to help remind me (and hopefully serve as  future reference for you too! :>).

Last week I was googling for the SQL Server cursor syntax and came across Brent V's blog entry about cursors.  Seems like I'm not the only one who can't remember the t-sql syntax for a cursor.

Ya, ya, I know, I know!  A lot of people HATE cursors, they think they are bad, horrd in fact, but in reality there are times when you just can't do something using "set theory" for which SQL excels at.  In those times, you have very little choice but to use a cursor.  At a very high level, cursors give you a way to loop over your table and do things based on fields, row by row. 

What's an example where you could use this?  Just today I wanted to find out all the tables in our databse without the LastModifiedDate field, after finding the tables missing that field, I would loop over that information adding a new column to just those tables missing it.  You could easily extend this by adding a an insert/update trigger at the samet ime.

Here are the individual pieces you need:

  1. Declare each variable you're going to need to get each time through the loop.
  2. Declare the cursor, you need to give it a name (and sometimes special flags)
  3. State the outer query, this is where you get the data you want to loop over row by row.
  4. Open the cursor, fetch the next row and put the values you want/need into the variables you declared in step 1 (if my word choice is a bit strange, that's ok, you'll see why soon).  Since we're dealing with SQL and not some high level, object oriented programming language with strong typing syntax, etc, you'll have to be careful of the order of your SELECT statement and the order in which you grab the variable you're interested in, order is very  important.
  5. Check for the status of your fetch, if it's ok (and you have data), begin your row-by-row work
  6. State your inner query, this is where you'd use the variables you declared (step 1) and set (step 4).  You're not limited to just sql statements, you can put prints, dynamic sql, exec procs, etc.  I suggest you put a BEGIN and END around your statements here, makes adding new sql later easier.
  7. Before the END statement, cut''n'paste your fetch into command from step 4 above.  Remember, this isn't C++/C#/VB.NET, so there's no simple for loop, you have to tell sql to fetch the next row of values into your variables where by it'll loop up to the WHILE statement above then check the fetch status.  If you run cursor and find it's taking FOREVER to run, chances are you forgot this step and it is indeed looping forever.  DOH! :>
  8. Lastly, you need to close and deallocate the cursor.  I'm not quite sure why they are two separate statements for this, I've never seen anyone want to do just one by itself.

Here's the shell of setting up a cursor

declare @{LoopVariable1} varchar(max)
declare @{LoopVariable2} int

declare {CursorName} cursor fast_forward for
{Your sql for the outer query here, make sure to bring back string and int column}
open {CursorName}

fetch next from {CursorName} into @{LoopVariable1}, @{LoopVariable2}
while @@FETCH_STATUS = 0

  {your SQL to do something with with the @{LoopVariable1} and @{LoopVariable2}}

  fetch next from {CursorName} into @{LoopVariable1}, @{LoopVariable2}

close {CursorName}
deallocate {CursorName}

 Here is a sample of the above steps. 

declare @TableNameToAddColumn varchar(max)    --table name to be set row by row
declare @sql varchar(max)   --dynamic sql to be used later on to alter the table schema

declare TablesMissingLastModifiedColumn cursor fast_forward for   --declare the cursor
--find all the tables missing the LastModified column
select as TableName
from sys.tables t
where not in 
  (--find out the tables which have the LastModifiedDate column
    from sys.columns c inner join sys.tables t on c.object_id = t.object_id
    where = 'LastModifiedDate'
  order by TableName
open TablesMissingLastModifiedColumn
fetch next from TablesMissingLastModifiedColumn into @TableNameToAddColumn
while @@fetch_status = 0
  set @sql = 'alter table ' + @TableNameToAddColumn + ' add LastModifiedDate datetime null'
  print @sql
  fetch next from TablesMissingLastModifiedColumn into @TableNameToAddColumn

--house keeping
close TablesMissingLastModifiedColumn
deallocate TablesMissingLastModifiedColumn

The cursor is declared with the fast_forward keyword cause, well, I'm only going forwards and therefore want to tell SQL Server that so it can make some optimizations for me.  Don't forget, if you're pulling back multiple fields/columns, that's cool, just make sure you put them in the "into" clause in the same order as the sql, otherwise you'll end up wit the wrong values in your sql variables.  If you find as your testing out your query/cursor, it's just not returning, make sure you have the status check AND the second fetch next  before the last end statement, that's the end of the loop and juuuuuuuust before you loop back around, you have to fetch the next row of data.  Ya, ya, I know, you'd think SQL Serer were smart enough to do that for you, but it's not (at least not today).  The exec(@sql) is commented out so not to screw your tables if/when you run this, but make this truly work, just uncomment that line and you're good to go.

I hope you can see cursor's aren't THAT difficult to create and use when their use is warranted.  Now go grab a coffee and get coding! :>



Just Geeks: Using a T-SQL Cursor in SQL Server, Brent V

MSDN: Transact-SQL Cursors

MSDN: Declare Cursor (Transact-SQL) (syntax)

Codeguru: Cursors, Ali Sufyan

Database Journal: Using SQL Server Cursors, Alexander Chigrik

Location: Blogs Parent Separator TechTidBits

2 comment(s) so far...

Re: SQL Server t-sql Cursors for the forgetful developer

Hi Peter,

Nice blog. You have a fun tone to your writing. Thanks for referencing my blog as well. I appreciate that!



By Brent Vermilion on   Wednesday, April 08, 2009 10:04 AM

Re: SQL Server t-sql Cursors for the forgetful developer

Thank you very much for compliment about the tone. I was worried last night maybe I was a bit over the top? But then figured naaaaaaa, it's just for fun and sharing info. I don't want to make it stuffy nor snobby.

re referencing your blog
Credit given where credit due. :>>>>

Thanks again and have a good day!

By phenry on   Wednesday, April 08, 2009 10:13 AM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Add Comment   Cancel 
Copyright 1999-2012 by   Terms Of Use  Privacy Statement