Using a T-SQL Cursor

Who is online? 0 guests and 0 members

Home » Articles » Using a T-SQL Cursor

Using a T-SQL Cursor

Comments (6)
Print

change text size: A A A

Published: 11/29/2009 by DustinRyan - Views: [1935]

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!

4.38

Avg: 4.38/5: (1 votes)

Comments (6)

TimMitchell said:

Cursors do indeed have a bad reputation among most T-SQL developers. However, there are a few circumstances (albeit rare) where a T-SQL cursor is the best solution to the problem. Row-by-row processing is expensive but occasionally necessary.

11/29/2009

Comment

paschott said:

Definitely more expensive, but not to be avoided altogether as you noted. We have several pretty complex pieces of TSQL that operate on one row at a time. Cursors help there to translate that into being able to operate on many rows, even if in sequence. Good reminder on @@FETCH_STATUS, too. I don't usually use anything other than the Success check so it helps job my memory seeing that again.

12/11/2009

Comment

dustinryan said:

I didn't say it should be avoided altogether. I simply pointed out that it was a slow, but sometimes necessary tool.

12/11/2009

Comment

jtarnott said:

It never hurts to understand all the tools available, so this explanation of how a cursor can be coded does have value. I would object, though, to the implication that cursors should be expected to be necessary very often at all. The example Dustin gives of retrieving the Identity column of an inserted row for use in subsequent correlations or logging may well have been effected with the use of the OUTPUT clause in the original insert. There may have been, no, there probably were other complications that led him to use the cursor, but for a simple case of just knowing the value, the following code may be a model for an alternative set-based approach: -- Create two tables to play with create table Table_1 (ID int identity, Field_1 varchar(255) null) create table Table_2 (ID int identity, Field_1 varchar(255) null) -- Insert four rows in first table Insert table_1 (Field_1) values('Something from table 1') Insert table_1 (Field_1) values('Something else from table 1') Insert table_1 (Field_1) values('Something more from table 1') Insert table_1 (Field_1) values('One more thing from table 1') -- Insert four rows in second table Insert table_2 (Field_1) values('Something from table 2') Insert table_2 (Field_1) values('Something else from table 2') Insert table_2 (Field_1) values('Something more from table 2') Insert table_2 (Field_1) values('One more thing from table 2') -- Look at the contents select * from table_1 select * from table_2 -- Declare a table variable to hold selected fields of the inserted data. Declare @Inserted table(ID int) --Insert with OUTPUT clause to capture all inserted IDs Insert table_1 Output Inserted.ID -- More fields may have been captured: , Inserted.Field_2 into @Inserted Select Field_1 from Table_2 t2 --What gets inserted to table_1 is from Table_2 where t2.ID %2 = 0 --Where the ID is an even number (2 and 4 in this case0 -- Look at data in both tables and the table variable based on the newly inserted IDs select i.id as Inserted_ID ,t1.id as Table_1_ID ,t1.Field_1 as Table_1_Fld_1 ,t2.id as Table_2_ID ,t2.Field_1 as Table_2_Fld_1 from @Inserted i join table_1 t1 on t1.id = i.ID join table_2 t2 on t2.Field_1 = t1.Field_1 -- Drop the tables Drop table Table_1 Drop table Table_2

12/22/2009

Comment

jtarnott said:

Oh, dear. That last post is impossible to read now that the formatting is gone from my SQL example. I've turned that comment into an "article" and submitted it for publication on BIDN.

12/22/2009

Comment

aabundez said:

Well, I just found out a use for cursors: filling in missing date ranges on a table with lots of unique ID's. Take a look: http://bit.ly/pcloQ8. I may be off base and there's a very good way of doing it in SSIS, but I just found using cursors saved me lots of time. I am anti-cursors in T-SQL as well, but here's one pro for T-SQL in the "T-SQL vs SSIS" debate.

8/26/2011

Comment

Add a new comment

Using a T-SQL Cursor

Using a T-SQL Cursor

Comments (6)

Most Recent Articles