Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 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


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

        End   

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 Asp.net, C#.net, 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

6 comments :

Suresh Dasari said...
This comment has been removed by the author.
Snaits 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
cursor
for
select
name,salary
from
emp
open mycur
declare @name varchar(50)
declare @salary int
fetch next from Mycur into @name,@salary
while @@FETCH_STATUS=0
begin
select
name,salary
from
emp
where
salary >=20000
end
close mycur
deallocate mycur

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

hari said...

same infinite loop issue as Marees said. :(

Anonymous said...

-- use Fetch next in while to avoid infinite loop

declare mycur
cursor
for
select
name,salary
from
emp
open mycur
declare @name varchar(50)
declare @salary int
fetch next from Mycur into @name,@salary
while @@FETCH_STATUS=0
begin
select
name,salary
from
emp
where
salary >=20000
fetch next from Mycur into @name,@salary
end
close mycur
deallocate mycur

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

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