Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to define and use Cursors in SQL Server 2000.

Author: Shahzad

This article will help you understand the basic idea of cursors and learn how to use these cursors.

Cursors can be considers as named result sets which allow a user to move through each record one by one. SQL Server 2000 provide 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

 

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.

 

Declare MyCur Cursor

For

Select * From Users

 

 

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

 

 

Open MyCur

 

 

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

--Othewise

--Fetch the next row as we did in the previous fetch              statement

 

End

   

 

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

 

 

 

 

Article Comments
This article solve my problem. Thank you very much. Do well...

Posted on 1/23/2007 11:51:49 PM by ashok vishvakarma

I want to know about the Cursors And I thinks you will help me

Posted on 2/5/2007 3:41:42 AM by sunil Raina

Pls send me an article on Cursor.

Posted on 2/7/2007 1:09:06 AM by Mukesh Sharma

it is very good explation for the cursor.thanks.

Posted on 3/23/2007 12:30:14 AM by Yadagiri Goud

Nice .very easy to understand.

I want to know some practical example when we really need cursor?

Posted on 3/28/2007 5:46:44 AM by ashis

can u explain me how to use curosrs in user interface(vb.net etc). are these mean only in back end or front end also
??????????

Posted on 3/29/2007 1:10:49 AM by RAJ KUmari

how to declare cursor in sql server? and why we use the cursors.

Posted on 5/8/2007 8:17:39 AM by sankar

what is the use of cursors?
what type of situations we use cursors?

Posted on 5/8/2007 8:19:27 AM by sankar

plz send the all details for that then we can use this in our application plz be

Posted on 5/8/2007 10:06:27 AM by gaurav

Good information

Posted on 5/11/2007 6:21:06 AM by Narasimha reddy

your examples helpful to me. but you little bit explan more example to me

Posted on 1/8/2008 11:29:12 AM by trimurthulu

very easy to understand
good art..

Posted on 1/18/2008 3:19:34 PM by bj

Please. send me detail article about CURSOR in Sql Server 2000

Posted on 1/23/2008 1:17:33 AM by Arpan Sen

very usefull article

Posted on 2/7/2008 1:59:30 AM by sateesh n

Thank you very much, this must be a fantastic one, please try to give other explanations also, please...., we are eager to read................................

Posted on 2/14/2008 9:21:56 AM by Bala

Fetch Next From Cursor Into @UserName, @Password

Here instead of Cursor keyword,name of created cursor will be given eg.MyCur

Posted on 3/10/2008 6:06:36 AM by sandhya

What is advantage of curser?
why we need curser?

Posted on 3/11/2008 11:40:21 AM by Kalicharan Yadav

Please send some more examples of using records

Posted on 3/13/2008 12:07:05 AM by Nageswara Rao.I

Nice article very nice article for the begineers, can you sir also guide how to pass values into @UserName, @Password parameters in this Cursor...

Posted on 4/4/2008 7:23:20 AM by Vishnu

I want to know if i want to fetch more columns in cursor using single variable. (rowtype cursor in oracle - equivalent in sql server)

Posted on 4/17/2008 11:54:08 AM by jayasri

it was really fantastic.
i am new to cursor and this article solve my problem

Posted on 4/21/2008 6:49:59 AM by Trideep patel

very easy to understand

Posted on 5/2/2008 2:46:16 AM by bharat

what is the real use of cursor in software industries,any more example of the same i m eager to know..................

Posted on 5/9/2008 12:33:44 PM by punish goyal

I want to know some practical example when we really need cursor?

Posted on 5/9/2008 12:35:29 PM by punish goel

Can u tell me which condition,we have to use Cursor & what is the Differences
between Strored Procedure and Cursor.Send the ARTICLE

Posted on 5/20/2008 7:45:02 AM by Jitendra Patel

can we create more than one cursor in a single name?

Posted on 5/26/2008 2:42:21 AM by bala

like tables,procedures,triggers
where cursors will be stored

Posted on 5/26/2008 2:43:32 AM by bala

How to use Cursor in ADO.net ( how to access cursor in front page)

Posted on 5/27/2008 7:47:58 AM by shiva.k

where cursors will be stored

your examples helpful to me. but you little bit explan more example to me


Posted on 6/13/2008 7:39:10 AM by Sonu Raj

what is the real use of cursor in software industries,any more example of the same i m eager to know?

Posted on 6/25/2008 6:43:27 AM by meenakshi

What is database Object?

Posted on 6/25/2008 6:45:31 AM by meenakshi

please tell me about the Cluster and Non Cluster Index?

Posted on 6/25/2008 6:47:02 AM by meenakshi

Can we attach a windows application program with a web program in .net?

Posted on 8/11/2008 4:34:42 AM by arun

Difeerence b/w Normalisation and De-Normalisation

Posted on 9/7/2008 6:39:40 AM by Anand j

The process of teaching is excellent. This is the best way of understanding about any topic

Posted on 11/12/2008 5:16:44 AM by Zubair Hasan Ansari

What kind of processes we can perform on the rows that we have fetched???..i mean like insertion, updation ..and what more.??

Posted on 11/17/2008 4:25:44 AM by Anil

this very good way to teach

Posted on 7/30/2009 6:50:20 AM by Rajeev

please find the result using cursor....

his is my table it contain 2 column

Dated Drive

07/23/09 09:49 AM A

07/24/09 09:49 AM A

07/25/09 09:49 AM A

07/26/09 09:49 AM B

07/27/09 09:49 AM A

07/28/09 09:49 AM A

07/29/09 09:49 AM C


o/p should be

from to drive
07/23/09 07/25/09 A

07/26/09 07/26/09 B

07/27/09 07/28/09 A

07/29/09 07/29/09 C


Please send solution urgent...............

Posted on 8/3/2009 1:35:29 AM by Ravi Naik

Difference between Primary & Foreign key

Posted on 11/24/2009 5:10:12 AM by sayali Deulkar

Hi Sayali deulkar,

Primary keys enforce entity integrity by uniquely
identifying entity instances. Foreign keys enforce
referential integrity by completing an association between
two entities.

In simple word we can say

Primary key is unique
primary key is not NULL and

foreign key is NULL
foreign key reference as Primary key in another table

If you need more description then please let me know
Thanks.

Posted on 11/24/2009 7:02:12 AM by zunnair

What is Cursor in sql & use of this Cursor .

Posted on 11/25/2009 11:39:28 AM by sayali Deulkar

Hi Sayali,

We all know that SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources

But Sayali here is a issue in cursors that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row. In the Cursor Performance article I discovered that cursors are over thirty times slower than set based alternatives.

For any other query let me know.

Thanks

Posted on 11/25/2009 11:55:54 AM by zunnair

Good Artical

Posted on 3/4/2010 4:03:14 AM by Arshad Khan

good article

Posted on 3/5/2010 5:50:29 AM by arunendra

Good but plz One exmple

Posted on 3/23/2010 8:00:05 AM by Rohit

nice one

Posted on 4/4/2010 1:57:10 PM by mukesh verma

Nice one

Posted on 4/21/2010 1:45:39 AM by ramu

what r the types of cursors in sql server

Posted on 8/10/2010 1:13:16 AM by Pulkesh

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< SQL Server 2000 and XML Integration.

Disclaimer - Privacy
© 2002-2017 DevASP.net