Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Using a T-SQL Cursor

  • 28 November 2009
  • Author: DustinRyan
  • Number of views: 8272
  • 0 Comments

In this article, I’m going to cover how to use a SQL Cursor, when you should use a SQL Cursor, and when you should not use a SQL Cursor. Basically, a SQL Cursor is a way to loop through a data set, row by row. Unfortunately, there is a huge downside to using a SQL Cursor: It’s slower than slow. If you use a Cursor to perform row by row operations on a huge data set, prepare to experience a big performance hit. The upside to a Cursor: There’s not really an upside. Sometimes it’s just necessary. Sometimes you really have no other option than to use a Cursor, which is why I am covering it here.

An example of when I recently used a SQL Cursor on a project for a client is a batch update of several tables that each contained identity columns. After inserting a row into a table, I retrieved the newly created identity column value (using SCOPE_IDENTITY) and inserted it into another table along with some values from a row in another dataset. It was slow, but it worked. Another example would be if I needed to execute a stored procedure against a single row at a time.

Now I want to share a couple tips with you before we dive into the syntax of a Cursor and start getting our hands dirty. The first tip is to try your best to limit the amount of data in your data set. Try using a WHERE clause to limit the amount of data the Cursor has to process. DO NOT do something crazy like SELECT * FROM TableHuge. It will take forever to run.

--First you declare your cursor and name it.

--Mine is named "CursorName" for this example.

DECLARE CursorName CURSOR

 

--Next, I declare my type of cursor. I'm using a FORWARD_ONLY cursor.

--That means my cursors cycles through the data set in 1 direction:

--From beginning to end. If I used a SCROLL type cursor, I could

--retrieve rows anywhere in the data set instead of just the next row.

--Maybe we'll cover that another day.

      FORWARD_ONLY

      FOR

     

--This is the data set my cursor will process row by row.

                   Select Column1, Column2, Column3

                   From Table

                   Where Column1 = 1

                   

--Open the cursor and declare the variables that will hold the values

--from the current row in my data set

      OPEN CursorName

    Declare @C1 int, @C2 decimal(9, 2), @C3 int

   

--Load the variables with the values from the next row in the data set

    Fetch Next from CursorName into @C1, @C2, @C3

   

--Begin processing!

    While (@@FETCH_STATUS <>-1)

    Begin

   

--This is where you do your row specific t-sql. The below insert command is just an example.  

                  Insert into Table2 (Column3, Column4)

                  Values (@C1, @C2)

                       

--Get the next row from the data set               

    Fetch Next from CursorName into @C1, @C2, @C3

   

--Once the cursor has run through all the rows of the data set,

--End, Close, and Deallocate the cursor.   

    End

    Close CursorName

    Deallocate CursorName

     

 

Let me just add one thing about @@FETCH_STATUS. @@FETCH_STATUS returns the status of the last row fetched from the data set by your cursor. If the @@FETCH_STATUS = 0, fetch was successful. If @@FETCH_STATUS = -1, fetch failed. If @@FETCH_STATUS = -2, the row fetched is missing. Also, @@FETCH_STATUS is a global to all cursor on a connection. so check out MSDN before you start using it so you don't get yourself into SQL trouble.

 

I hope someone found this helpful and if anyone has anything to add, just leave a comment!

Print
Categories: Miscellaneous
Tags:
Rate this article:
4.3
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.