RSS Feed

Cursors in T-SQL

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.

Cursors

Let’s assume that we have some Table called „UserData” with typical Columns:

  • ID
  • Name
  • Date
  • isActive

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

Result:

  • 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

Result:

  • 2
  • 3

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:

  1. Create temporary variables for data fetched from result row
  2. Create Cursor and assing a Select Query to it
  3. Open Cursor , this commands fils Cursor with data returned from assigned Query
  4. Iteration on records with Fetch function , assign data to temporary  variables
  5. Run procedure with temporary variables as a parameters
  6. repeat step 4 and 5
  7. Close Cursor , disposing resources
  8. 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

2.Create Cursor

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.

3.Open Cursor.

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.

Cursors are also available in  MySql and Oracle.

Advertisements

One Comment on “Cursors in T-SQL”

  1. Ratio says:

    Dlaczego podrywasz młode dziewoje na serwisie zts moodle ? :p


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s