Thursday, 23 August 2007

T-SQL Cursor syntax

I've been asked a few times about the cursor syntax (and actually myself have been forgetting too). I know the SQL books online has syntax for everything, but apparently it is a bit hard to read some times. Anyway, post this here so it's handy for myself and the others. :)

DECLARE some_cur CURSOR FOR
SELECT Column1, Column2, Column3 FROM TableName

OPEN some_cur

DECLARE @col1 INT, @col2 NVARCHAR(50), @col3 BIT

FETCH NEXT FROM some_cur INTO @col1, @col2, @col3

WHILE (@@fetch_status <> -1)
BEGIN

-- Do whatever you want

FETCH NEXT FROM some_cur INTO @col1, @col2, @col3

END

CLOSE some_cur
DEALLOCATE some_cur
Posted by Gypsy at 3:20 pm
Labels:

3 comments:

  1. Anonymous21 January 2009 at 8:35 am

    Thank you, this post saved me a lot of time. :-)

    ReplyDelete
  2. Unknown1 October 2009 at 8:22 pm

    Thanks, your post was exactly what i needed. easy, simple and to the point. :)

    ReplyDelete
  3. Vuyiswa Maseko24 August 2010 at 5:35 am

    Very Good Post, i thought i knew how to use Cursors , until i met a very complex scenario and i realized that it has been long since i used them , i cant even remember the sequence and i looked dumber.

    Thanks for sharing

    ReplyDelete

Subscribe to: Post Comments (Atom)