In this blog describe the concept of cursor in sql server and how to create cursor in sql server, types of cursor. Here, I’m trying to demonstrate the good examples of cursors.

Cursors are database objects used to traverse the results of an SQL query. Cursor is a database objects to retrieve data from a result set one row at a time, instead of the sql commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row. It is mainly used in stored procedures and triggers in database. Cursor is like as looping concept. It also used for loop. To use the cursor in sql server it has life cycle:

Life cycle of cursor

Declare

A cursor is declared by defining the SQL statement that returns a result set.

Syntax

 

DECLARE <cursor_name> CURSOR FOR <select statement>

Example
DECLARE Product_Cursor CURSOR
for
Select * from PRODUCT
where ProductPrice>=10000;
Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

Syntax

OPEN <cursor_name>

Example

OPEN Product_Cursor;

Fetch

When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

Syntax
FETCH
[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @n_varaible} | RELATIVE {n | @n_varaible} ]
FROM ]
{ cursor_name }
[ INTO @variable_name1, @variable_name2 … ]
Example

FETCH Product_Cursor;

Close

After data manipulation, we should close the cursor explicitly.

Syntax

CLOSE <cursor_name>

Example

CLOSE Product_Cursor

Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax

DEALLOCATE <cursor_name>

Example

DEALLOCATE Product_Cursor;

Example of cursor in sql server

Declare @P_ID as int
Declare @P_Name as varchar(50)
Declare @P_Price as int
Declare Product_data CURSOR FOR
 
Select ProductID, ProductName ,ProductPrice from  Product
 
OPEN Product_data
    FETCH NEXT FROM Product_data INTO @P_ID, @P_Name, @P_Price
        WHILE @@FETCH_STATUS = 0
        BEGIN
                     PRINT 'Product ID : '+ convert(varchar(20),@P_ID)+', Product Name :'+convert(varchar(50),@P_Name)+ ', Product PRICE : '+convert(varchar(20),@P_Price)
            FETCH NEXT FROM Product_data
            INTO @P_ID, @P_Name, @P_Price
           
        END
    CLOSE Product_data
DEALLOCATE Product_data;


  Modified On Sep-18-2014 01:24:24 PM

Leave Comment