Simple SQL Cursor Example

Simple SQL Cursor Example

If you’re reading this blog, chances are, you know how to write/use a SQL cursor. But this is more for my benefit, and maybe even yours. Just a quick cursor example, so that I can easily reference the syntax if I ever need to quickly look it up, since I always seem to forget it.  Enjoy.

DECLARE @CONTACTID INT 

--will need to declare at least one variable
--in this case, to store values when iterating through cursor
DECLARE SIMPLE_CURSOR CURSOR FOR SELECT ID FROM CONTACT OPEN SIMPLE_CURSOR FETCH NEXT FROM SIMPLE_CURSOR --Start the cursor INTO @CONTACTID WHILE @@FETCH_STATUS = 0 --while there is a loaded record, keep processing BEGIN --do whatever you need to do print ('This is where the magic happens! Do whatever you need to do (update/insert/delete/stored proc/etc.') FETCH NEXT FROM SIMPLE_CURSOR INTO @CONTACTID --fetch next record END CLOSE SIMPLE_CURSOR --close and deallocate DEALLOCATE SIMPLE_CURSOR
By | 2011-02-02T22:43:43+00:00 February 2nd, 2011|Categories: SQL Server|Tags: , |11 Comments

About the Author:

11 Comments

  1. Kinjal September 4, 2012 at 5:59 am - Reply

    Short and quick understandable explanation… 🙂

    • Brent September 4, 2012 at 2:11 pm - Reply

      Glad it helped Kinjal 🙂

  2. mehrnoosh September 24, 2012 at 3:19 am - Reply

    thanks,very good

  3. Shanaka October 30, 2012 at 12:43 pm - Reply

    hi,
    i am not familiar with the sql CURSOR. can you just give a idea, why we are using this CURSOR option on sql.

    thx#
    Shanaka

    • Brent October 30, 2012 at 2:08 pm - Reply

      Hi Shanaka,

      Cursors can be handy when you need to iterate over some data set of records, and perform an action for each record. For example if you want to update each row with some sort of calculation, etc.

      There are many ways to do this, but the SQL Cursor is one of them.

      Hope that helps

  4. W.Devonshire September 21, 2013 at 4:19 am - Reply

    Many thanks, can never remember the cursor syntax 🙂

    BillD

    • brent September 21, 2013 at 2:03 pm - Reply

      No problem, I had the same problem several times. Leading to this post… 🙂

  5. cinafre September 25, 2013 at 2:52 am - Reply

    I have multiple values in one cell in a table which are separated by an space from each other. this is how my table looks like, there is a space in between each string in every cell:

    column1 column2
    Data1 Data2 Related1 Related2

    I want to have another table in which “Data1” and “Related1” are in one row, “Data2” and “Related2” are in another row, because they are related to each other:

    column1 column2
    Data1 Related1
    Data2 Related2
    How can I do that?

  6. Mritunjay kumar May 23, 2014 at 12:17 am - Reply

    It is quick Understable example

  7. Sharad Patil December 15, 2014 at 6:03 am - Reply

    Its Very useful code
    Thank u!

Leave A Comment