Cursor is a Database object which allows us to process each row and manipulate its data. A Cursor is always associated with a Select Query and it will process each row returned by the Select Query one by one.
Using Cursor we can verify each row data, modify it or perform calculations which are not possible when we get all records at once.
A simple example would be a case where you have records of Employees and you need to calculate Salary of each employee after deducting Taxes and Leaves.
Example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE PrintUser_Cursor
AS
BEGIN
 SET NOCOUNT ON;
 
 --DECLARE THE VARIABLES FOR HOLDING DATA.
 DECLARE @UserId INT
 ,@Name VARCHAR(1000)
 ,@UserType VARCHAR(1000)
 
 --DECLARE AND SET COUNTER.
 DECLARE @Counter INT
 SET @Counter = 1
 
 --DECLARE THE CURSOR FOR A QUERY.
 DECLARE PrintUsers CURSOR READ_ONLY
 FOR
 SELECT USER_ID, USERNAME, USER_TYPE
 FROM USER_LOGIN
 
 --OPEN CURSOR.
 OPEN PrintUsers
 
 --FETCH THE RECORD INTO THE VARIABLES.
 FETCH NEXT FROM PrintUsers INTO
 @UserId, @Name, @UserType
 
 --LOOP UNTIL RECORDS ARE AVAILABLE.
 WHILE @@FETCH_STATUS = 0
 BEGIN
 IF @Counter = 1
 BEGIN
 PRINT 'User ID' + CHAR(9) + 'Name' + CHAR(9) + CHAR(9) + CHAR(9) + 'User Type'
 PRINT '------------------------------------'
 END
 
 --PRINT CURRENT RECORD.
 PRINT CAST(@UserId AS VARCHAR(10)) + CHAR(9) + CHAR(9) + CHAR(9) + @Name + CHAR(9) + @UserType
 
 --INCREMENT COUNTER.
 SET @Counter = @Counter + 1
 
 --FETCH THE NEXT RECORD INTO THE VARIABLES.
 FETCH NEXT FROM PrintUsers INTO
 @UserId, @Name, @UserType
 END
 
 --CLOSE THE CURSOR.
 CLOSE PrintUsers
 DEALLOCATE PrintUsers
END
GO

Output.



Leave a Reply

Your email address will not be published. Required fields are marked *