aspdotnet-suresh offers articles and tutorials,csharp dot net, articles and tutorials,VB.NET Articles,Gridview articles,code examples of 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Cursors in SQL Server

May 10, 2010
Cursors can be considers as named result sets which allow a user to move through each record one by one. SQL Server 2000 provides different types of cursors to support different type of scrolling options. 
When we use a Select statement in a stored procedure to get some result, we can get a collection of all the records which satisfy the given criteria. This result set can be returned back as a whole to the client application. We can’t move through each individual record one by one in this situation inside a stored procedure. Therefore, whenever we find some situation where we need to process each record individually we can use cursors. 

In order to work with a cursor we need to perform some steps in the following order
1.         Declare  cursor
2.         Open cursor
3.         Fetch row from the cursor
4.         Process fetched row
5.         Close cursor
6.         Deallocate cursor
So, let’s take a look at these steps in a little detail

1)       First of all we need to declare a cursor with the help of Declare statement and in order to specify the result set contained by this cursor we use Select statement. For example we can define a cursor named MyCur and we can use a table named Users having two columns Username, and Password

2)       Declare   MyCur Cursor for select * from Users  

3)       Next, we need to open the cursor so that we’ll be able to use it

Open MyCur  

4)       Now, fetch first row from this cursor and loop through the cursor records until the specified criteria is found
        Declare @Username varchar(50)
                 Declare @Password varchar(50)
                 Fetch Next From Cursor Into @Username,@Password

    While @@Fetch_Status=0

        --Check if appropriate row was found then process it
        --Fetch the next row as we did in the previous fetch statement


1)        When we have worked with the cursor, we’ll close it and deallocate it so that there will remain no reference to this cursor any more.
Close MyCur

Deallocate MyCur

2)       Note: Fetch_Status is the default parameter to cursor While @@Fetch_Status=0 indicates until last record

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest,, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email


Suresh said...
This comment has been removed by the author.
Stian Sveen said...

Hi Suresh,
While you *can* use cursors whenever you find some situation where you need to process each record individually, I wouldn't generally recommend it.

Cursors are hogging memory and locking tables, so that other processes may not access these tables.

So if you can, use a while loop, user defined functions or even a common table expression if it fits your need.

Anonymous said...

Can u give one proper Example of cursor with stored procedure showing result in gridview

Marees said...

declare mycur
open mycur
declare @name varchar(50)
declare @salary int
fetch next from Mycur into @name,@salary
while @@FETCH_STATUS=0
salary >=20000
close mycur
deallocate mycur

Condition checks successfully and also run successfully.....
but infinite loop occurred..........
correct the error
give me error free code

pradeeP"BHUMI........." said...


hari said...

same infinite loop issue as Marees said. :(

Give your Valuable Comments

Other Related Posts

© 2010-2012 All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.