A Cursor
A cursor is a set of rows together with a pointer that identifies a current row.
In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis.
Steps to use a Cursor:
(1) Declare Cursor: Before using cursor, you first must declare the cursor and define the query used to build the result set on which the cursor operates.
DECLARE cursor_name CURSOR
FOR select_statement
(2) Open Cursor: Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax:
OPEN cursor_name
(3) Fetch From Cursor: Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax:
FETCH FROM cursor_name
[INTO @variable1_name,@variable2_name,...n]
[INTO @variable1_name,@variable2_name,...n] - allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.
(4) Close Cursor: When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open.
To close a cursor, you can use the following syntax:
CLOSE cursor_name
(5) Deallocate Cursor: When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor.
To deallocate a cursor, you can use the following syntax:
DEALLOCATE cursor_name
Example:
-- DECLARE two variables
DECLARE @fName varchar(50), @lName varchar(50)
-- Declare cursor
DECLARE cursorName CURSOR
FOR Select firstName, lastName FROM myTable
-- open the cursor
OPEN cursorName
-- Fetch a row from cursor into two variables
FETCH NEXT FROM cursorName
INTO @fName, @lName
-- print the fetched data
PRINT @fName + ' ' + @lName
-- @@FETCH_STATUS in below line Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection
-- Returns 0 : FETCH statement was successful.
-- Returns -1 : FETCH statement failed or the row was beyond the result set.
-- Returns -2 : Row fetched is missing.
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursorName
INTO @fName, @lName
PRINT @fName + ' ' + @lName -- print the name
END
-- close the cursor
CLOSE cursorName
-- Deallocate the cursor
DEALLOCATE cursorName
-----------------------------------------------------------------
Hope you will get clear with the basics of using a cursor...
Thank You...