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

Page: www.Riteshk.blogspot.com

---

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