Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

SQL Server Cursor | Cursor Life Cycle | Example

What is a SQL Server Cursor?
A SQL Server Cursor is a set of T-SQLlogic to loop over a pre-determined number of rows one at a time and the purpose for the cursor may be to update one row at a time. 

 OR

The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time. Cursors are used when the user needs to update records in a singleton fashion or in a row by row manner, in a database table.

SQL Cursor Life Cycle
The following steps are involved in a SQL cursor life cycle.
1.     Declare Cursor - A cursor is declared by defining the SQL statement.
2.     Open Cursor-  A cursor is opened for storing data retrieved from the result set.
3.     Fetch Cursor- When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
4.     Close Cursor - The cursor should be closed explicitly after data manipulation.
5.     Deallocate Cursor – The cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.
Syntax -

DECLAREcursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR  
     FORselect_statement  
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] 
[;] 
--Transact-SQL Extended Syntax 
DECLAREcursor_name CURSOR [ LOCAL | GLOBAL ]  
     [ FORWARD_ONLY | SCROLL ]  
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]  
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]  
     [ TYPE_WARNING ]  
     FORselect_statement  
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ] 
[;]

As an Example,
------CREATE PROCEDURE------------------
CREATEPROCEDURE sp_Cursor_Employee
ASBEGIN
 DECLARE@EmpName VARCHAR(50)
 DECLARE@EmpDepartment VARCHAR(50)

 DECLARECursor_Employee CURSOR FOR
 SELECT EmpName, EmpDepartment FROMEmployee

-----OPEN CURSOR--------
 OPENCursor_Employee

-----FETCH CURSOR-------
 FETCHCursor_Employee INTO @EmpName,@EmpDepartment
 WHILE(@@fetch_status=0)
 BEGIN
              PRINT '* EmpName= '+@EmpName
              PRINT '  EmpDepartment= '+@EmpDepartment

  --FETCH CURSOR FOR NEXT ROWS
  FETCHCursor_Employee INTO @EmpName, @EmpDepartment
 END

-------CLOSE CURSOR--------
CLOSECursor_Employee

-- DEALLOCATE CURSOR
DEALLOCATECursor_Employee

END

Share the post

SQL Server Cursor | Cursor Life Cycle | Example

×

Subscribe to Angular 2 Javascript Asp.net C# Sql Server Knockoutjs Angularjs Kendo Ui Html 5 Web Api Wpf Wcf Etc.

Get updates delivered right to your inbox!

Thank you for your subscription

×