Nigel Whitworth's Development Site

You are currently not logged in.

SQL Server Cursors

Cursors provides a while to cycle through a collection and then act on the values returned. First , in your stored procedure, you will need to define all the variables that are going to be used to store currently selected record data. To do this, use the declare statement.

    Declare @name	varchar(255)
    Declare @tele varchar(255)

The example above declares temporary variables for an imaginary telephone directory. Next we define the cursor like in the example below. Declare the name then criteria for the records to exist in the cursor.

     declare crs_addresses cursor for
         Select name,tele from Directory     ' The criteria for the records to be returned.
         for update                          ' Optional, this is used to denote whether the records will be updated.

Now that you've declared the curor, you will need to open it, this is done simply by using the open command followed by the name of the cursor so the code will look like :-

    open crs_addresses

It's recommended that you work from the beginning to the end but you can fetch records from the end first. To fetch from the end use Last instead of First.

    Fetch First from crs_addresses into @name,@tele

If we want to cycle through the cursor, we'd need to put in a loop, the best kind of loop is a while. The condition that we will be checking is that the cursor flag is a 0. If its -1 then we've come to the end or beginning of the cursor. If the valus is -2 then the record doesn't exist anymore. The cursor flag is @@fetch_status, the code below shows us cycling through the recordset and displaying the details in the Query Analyzer window.

    while @@fetch_status = 0
        begin
            print @name + " " + @tele
            fetch next from crs_addresses into @name,@tele
        end

Once we've finished with the cursor, we need to clear it and delete it so that the resources now become free. This is done by :-

    close crs_addresses
    deallocate crs_addresses

This is quite a simple explanation of cursors which illustrates what you can do. The main area missing from this page is the different types of cursors that you can have, e.g. Readonly, dynamic, static, forward only.... In brief, cursor types are specified in the declare cursor statement. For example if we wanted a static cursor the code would look something like ;-

     declare crs_addresses cursor static for ' Notice the addition of the static keyword.
         Select name,tele from Directory     
         for update

The full code is :-


    Declare @name	varchar(255)
    Declare @tele varchar(255)

    declare crs_addresses cursor for
        Select name,tele from Directory     
        for update   

    open crs_addresses
		
    while @@fetch_status = 0
        begin
            print @name + " " + @tele
            fetch next from crs_addresses into @name,@tele
        end

    close crs_addresses
    deallocate crs_addresses

If you intend to vote or make a comment, please enter the security code.

Make a comment

*Comments are the views of individuals, they may or may not be correct.
All comments are reviewed and accepted or rejected.
If you give an email address, you will be sent an email when someone makes a comment on this page.*

Name :
Email :
Comments :

Only name and Code is compulsory.