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.
--This is the data set my cursor will process row by row.
Select Column1, Column2, Column3
Where Column1 = 1
--Open the cursor and declare the variables that will hold the values
--from the current row in my data set
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
While (@@FETCH_STATUS <>-1)
--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
--Once the cursor has run through all the rows of the data set,
--End, Close, and Deallocate the cursor.
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!