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 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
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.
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
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
Subscribe by RSS
Subscribe by Email
6 comments :
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.
Can u give one proper Example of cursor with stored procedure showing result in gridview
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
same infinite loop issue as Marees 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
Note: Only a member of this blog may post a comment.