Cursors in T-SQLPosted: 2010/03/13
Lately, I have started an internship in one of the IT Companies located in Wroclaw. I am really happy because this is a great opportunity to catch “real job” experience. Academic projects are a different kind of story.
I had a task to implement some MsSql queries and some operations on retrieved results. My first thought was to implement a simple for loop, iterate thought the result collection, do something on each result, „voila” .
Sure, Easier said than done.
Simple loop thought the results of SQL Query is great if we want to do something with a bunch of the data, but what should we do when with every iteration we want to do a specific operation on exactly one record. There is a problem because We would have to track the index of the current record. This would take a lot of effort to write test, etc. If you don’t want to waste a lot of time try the “Cursors”. They are ideal for this kind of a situation. They are similar to iterator in collections. You can fetch records, one by one and do some operations on them.
Let’s assume that we have some Table called „UserData” with typical Columns:
The simplest sql query ….
SELECT * FROM UserData
will return :
- 1 , Michał Franc , 2008-10-10 , 1
- 2 , Stefan Romański , 2006-01-01 , 1
- 3 , Maria Kozłowska , 2005-04-04 , 1
This is my test Data [Those are my secret personalities on the Net ].
Let’s ask for the name of the users with Date value before year 2007. This is another query falling to the „simple” category.
SELECT Name FROM USerData Where DATEDIFF(day,’2007-01-01′,Date) < 0
- Stefan Romański
- Maria Kozłowska
Let’s create a procedure to take an ID as a parameter and set the isActive column to 0. This procedure is used to set all users with Date before year 2007 to isActive status 0.
Lets Create Query for ID’s .
SELECT ID FROM DaneUzytkownikow Where DATEDIFF(day,’2007-01-01′,Data) < 0
We have ID’s of inactive users. We will use the cursors now to run a procedure for every ID.
Before going further let me describe you how to use Cursors:
- Create temporary variables for data fetched from result row
- Create Cursor and assing a Select Query to it
- Open Cursor , this commands fils Cursor with data returned from assigned Query
- Iteration on records with Fetch function , assign data to temporary variables
- Run procedure with temporary variables as a parameters
- repeat step 4 and 5
- Close Cursor , disposing resources
- Cursor Deallocation
1 DECLARE @UserId int 2 3 Declare @Cursor Cursor 4 5 Set @Cursor = Cursor FOR SELECT ID FROM UserData Where DATEDIFF(day,'2007-01-01',Data) < 0 6 7 Open @Cursor 8 9 Fetch Next From @Cursor Into @UserId 10 11 While (@@FETCH_STATUS = 0) 12 13 Begin EXEC SetInactive @Id = @UserID 14 15 Fetch Next From @Cursor Into @UserID End 16 17 Close @Cursor Deallocate @Cursor
1.Declaring Temporary Variable @UserId
DECLARE @UserId int – This variable on each iteration will
Declare @Cursor Cursor
Set @Cursor = Cursor
For SELECT ID FROM UserData Where DATEDIFF(day,’2007-01-01′,Data) < 0
– We have to assign sql query.
Open @Cursor – Assigned Query is executed.
4.Iterating through records.
We have to store data from row in temporary variable
Fetch Next From @Cursor Into @UserId
While (@@FETCH_STATUS = 0) – The while loop will iterate till last row returned from the query.
5.Executing Procedure with temporary variable as a parameter
Begin EXEC SetInactive @Id = @UserID
After executing the procedure we need to fetch next data.
Fetch Next From @Cursor Into @UserID End
7 i 8.Closing and Deallocating Cursor
Close @Cursor Deallocate @Cursor
And that would be all its quite simple and easy. Cursors are very useful in lot of scenarios.