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;
Leave Comment