Wednesday, October 05, 2005

Basic CURSOR example in SQL server

Basic CURSOR example in SQL server

 

Basic Syntax of Cursor

 

--Declare cursor in declare section

DECLARE CURSOR

--Open cursor for operation    

OPEN CURSOR

FETCH CURSOR

--Fetch record till end

WHILE  @@FETCH_STATUS =0

BEGIN

                                    USE CURSOR

END

--Close cursor

CLOSE CURSOR

--Remove from the memory

DEALLOCATE CURSOR

 

Different fetch statement

 

-- Fetch the last row in the cursor.

FETCH LAST FROM authors_cursor
-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM authors_cursor
-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM authors_cursor
-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM authors_cursor
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor

Use Cursor to print database table records

 

IF EXISTS (SELECT name FROM sysobjects

      WHERE name = 'ReadData' AND type = 'P')

   DROP PROCEDURE ReadData

GO

 

CREATE PROCEDURE ReadData

AS

BEGIN

            DECLARE @STRINGNAME NVARCHAR (3999)

           

            DECLARE CUR_NAME CURSOR FOR

SELECT USERNAME FROM TBLUSERINFO

 

            OPEN CUR_NAME

                        FETCH NEXT FROM CUR_NAME INTO

@STRINGNAME

                        WHILE @@FETCH_STATUS =0      

           

            PRINT @STRINGNAME

                        BEGIN

                                    FETCH NEXT FROM CUR_NAME INTO

@STRINGNAME

            END

            CLOSE CUR_NAME

            DEALLOCATE CUR_NAME

END   

GO

 

Use Cursor to return Set of Data/Records

 

IF EXISTS (SELECT name FROM sysobjects

      WHERE name = 'ReturnDataSet' AND type = 'P')

   DROP PROCEDURE ReturnDataSet

GO

 

CREATE PROCEDURE ReturnDataSet

(

    @CUR_DATASET            CURSOR VARYING OUTPUT

)

AS

BEGIN

            SET @CUR_DATASET = CURSOR FOR

                        SELECT USERNAME FROM TBLUSERINFO

 

END   

GO

 

Check cursor status

 

IF EXISTS (SELECT name FROM sysobjects

      WHERE name = 'ReadCursor' AND type = 'P')

   DROP PROCEDURE ReadCursor

GO

 

CREATE PROCEDURE ReadCursor

(

@CUR_STATUS            NVARCHAR (200) OUTPUT,

@CUR_DATASET            CURSOR VARYING OUTPUT

)

AS

BEGIN

DECLARE @RETURN_CUR  CURSOR

EXEC ReturnDataSet @RETURN_CUR OUTPUT

           

IF CURSOR_STATUS ('VARIABLE','@RETURN_CUR') <= 0

            BEGIN

                        SET @CUR_STATUS =

'NO DATA AVAILABLE IN CURSOR.'           

            END

ELSE

            BEGIN

                        SET @CUR_DATASET = @RETURN_CUR

            END

 

END   

GO

 

 



Ritesh Kumar Kesharwani

A D I T I , B A N G A L O R E
Software Professional
Cell  : 91-9845657636


Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.

6 comments:

Anonymous said...

Ich entschuldige mich, aber meiner Meinung nach lassen Sie den Fehler zu. Ich biete es an, zu besprechen. Schreiben Sie mir in PM. viagra bestellen levitra bayer [url=http//t7-isis.org]levitra online[/url]

Anonymous said...

Perdonen, he pensado y ha quitado el pensamiento [url=http://csalamanca.com/tag/comprar-viagra/ ]viagra generico colombia [/url] Soy seguro que es el error. viagra espanola

Anonymous said...

A mio parere, si sbaglia. Io propongo di discuterne. Scrivere a me in PM, ti parla. [url=http://lacasadicavour.com/tag/cialis-online/ ]acquisto cialis [/url]Finora, tutto bene. http://lacasadicavour.com/tag/cialis/ generico cialis Viene raggiunto il maggior numero di punti. Ottima idea, condivido.

Anonymous said...

Hay un sitio al tema, que le interesa. [url=http://csalamanca.com/comprar-viagra-generico/ ]viagra es con receta [/url] En esto algo es la idea excelente, es conforme con Ud. http://csalamanca.com/tag/comprar-viagra/ viagra 50 mg

Anonymous said...

ok, cool... i just added even more new emo backgrounds for my blog
http://www.emo-backgrounds.info

Anonymous said...

Hymenolepis nana http://soundcloud.com/buy-clomid-online how to take clomid Farnsworth how to take clomid
internal ear [url=http://soundcloud.com/buy-clomid-online]clomid
[/url] dimethylphenol http://subscene.com/members/Buy-Clomid-_2D00_-Online-Pharmacy.aspx clomid Kleffner clomid
vitreous table [url=http://subscene.com/members/Buy-Clomid-_2D00_-Online-Pharmacy.aspx]buy clomid
[/url]