Database Cursor

What is cursor?

Cursor is a database control structure to travels a data set or table row by row. Its as like as loop statement
used in may programming language.

We need to do the following to use cursors:

1. Declare a cursor.
2. Open the cursor.
3. Fetch the data into local variables row by row.
4. Close the cursor after finished.

Sample Cursor Format

DECLARE @Temp TABLE (

      CustomerNo        INT,

      CustomerName      NVARCHAR(100),

      InvoiceNo         INT

)

INSERT into @Temp VALUES(1,'Mr. Smith',100001)

INSERT into @Temp VALUES(2,'Mr. John',100002)

INSERT into @Temp VALUES(3,'Mr. Devid',100003)

DECLARE @Name NVARCHAR(100)

DECLARE dbCursor CURSOR FOR 

SELECT      CustomerName

FROM  @Temp

OPEN dbCursor  

FETCH NEXT FROM dbCursor INTO @Name  

WHILE @@FETCH_STATUS = 0  

BEGIN        

         PRINT(@Name)  

       FETCH NEXT FROM dbCursor INTO @Name  

END  

CLOSE dbCursor 

DEALLOCATE dbCursor

Output

Mr. Smith

Mr. John

Mr. Devid