Whenever you execute a select statement, it returns you a set of rows. The entire set is one thing and not a selection of individual rows. This output is useful for the batch-processing application but less appealing for applications where the user might want to work with rows at a time. The solution to this problem is Cursors. A cursor is a set of rows together with a set of pointers that help in identifying the current row. Cursors are generally used through stored procedures and triggers. The data retrieved by the cursor is stored in the temdp database.

Working with the cursor is a five step process:

·         Declare Cursor

·         Open

·         Fetch

·         Close

·         Deallocate

Declare Cursor

A cursor must be declared before it is used. The DECLARE CURSOR statement is used to declare the cursor and to set the storage and basic properties of the cursor.

Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR SELECT_STATEMENTS

[FOR {READ ONLY | UPDATE [OF column_name] } ] 

where 

·         cursor_name : Name of the cursor.

·         INSENITIVE : This is to create a temporary table just for this cursor.

·         SCROLL : It specifies that all the options of the FETCH statement are supported. If SCROLL is Omitted the cursor supports only FETCH NEXT.

·         READ ONLY : It prevents any update through the cursor.

·         UPDATE : It specifically states that the cursor should allow modification.

·         SELECT_STATEMENTS : It is the standard T-SQL statement that is supplying the rows for the cursor. 

Example

DECLARE Product_Cursor CURSOR

for

Select*from ProductTable

where price>=10000 

Open Cursor

Open statement is used to open a cursor before it is used.

Syntax:

OPEN cursor_name

Example

OPEN Product_Cursor

If the cursor was declared with the INSENSITIVE or STATIC keyword then the OPEN statement creates a temporary table in the tempdb database for holding records.These tables are automatically created by the SQL Server and they are also deleted by the the Server once the cursor is closed.

Fetch

FETCH is used to retrieve the data from the cursor into variables so that you can work with the data.

Syntax:

FETCH

[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @n_varaible} | RELATIVE {n | @n_varaible} ]

FROM ]

{ cursor_name }

[ INTO @variable_name1, @variable_name2 … ] 

Example

DECLARE @PRODUCTNAME varchar(50)
DECLARE Product_Cursor  CURSOR
for
Select PRODUCTNAME from ProductTable
where price>=10000
  
OPEN Product_Cursor
  
FETCHNEXTFROM Product_Cursor
INTO @PRODUCTNAME
Print @PRODUCTNAME
WHILE@@fetch_status= 0
BEGIN
      FETCHNEXTFROM Product_Cursor
      INTO @PRODUCTNAME
      Print @PRODUCTNAME
END

Output

  Close Cursor

When the work is over with the cursor then you should execute a CLOSE statement. This statement frees all the rows that are being held by the cursor but it does not destroy the cursor.

Syntax:

CLOSE cursor_name

Example

CLOSE Product_Cursor 

Deallocate

DEALLOCATE statement removes the definition of the cursor from the SQL server.

Syntax:

DEALLOCATE cursor_name

Example

DEALLOCATE Product_Cursor

EXAMPLE

DECLARE @PRODUCTID int,@PRODUCTNAME varchar(50),@PRICE int
DECLARE PRODUCTCURSOR CURSOR
STATIC
FOR
SELECT PRODUCTID,PRODUCTNAME,PRICE FROM ProductTable
OPEN PRODUCTCURSOR
IF@@cursor_rows> 0
BEGIN
      FETCHNEXTFROM PRODUCTCURSOR
      INTO @PRODUCTID,@PRODUCTNAME,@PRICE
      WHILE@@fetch_status= 0
      BEGIN
PRINT'ID : '+convert(varchar(20),@PRODUCTID)+', Name :'+@PRODUCTNAME+', PRICE : '+convert(varchar(20),@PRICE)
            FETCHNEXTFROM PRODUCTCURSOR
            INTO @PRODUCTID,@PRODUCTNAME,@PRICE
      END
END
  
CLOSE PRODUCTCURSOR
DEALLOCATE PRODUCTCURSOR

Output