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
