Home > DeveloperSection > Blogs > CURSOR in SQL Server

CURSOR in SQL Server


Database Database 
Ratings:
0 Comment(s)
 2894  View(s)
Rate this:

CURSOR in SQL Server

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;



Don't want to miss updates? Please click the below button!

Follow MindStick