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 ….

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
DECLARE@UserIdint 
Declare@CursorCursor 
Set@Cursor=CursorFORSELECT ID FROM UserData Where DATEDIFF(day,'2007-01-01',Data) <0
Open@Cursor  Fetch Next From @CursorInto @UserId  
While (@@FETCH_STATUS=0)
Begin
        EXEC SetInactive @Id=@UserID Fetch Next From @CursorInto @UserID
End
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.