First thing first. Usage of Cursors is not encouraged in SQL Server as they are slow. You may go with WHILE loop if you need to iterate through a recordset.Cursor is a database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time.So this is a row by row operation instead of a set based operation.
SQL Server Cursor Components: Cursors include following components:
1) DECLARE statements - Declare variables used in the code block.
2) SET\SELECT statements - Initialize the variables to a specific value.
3) DECLARE CURSOR statement - Populate the cursor with values that will be evaluated.
4) OPEN statement - Open the cursor to begin data processing.
5) FETCH NEXT statements - Assign the specific values from the cursor to the variables
NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement.
WHILE statement - Condition to begin and continue data processing.
BEGIN...END statement - Start and end of the code block.
CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
DEALLOCATE statement - Destroys the cursor
Below is an example of a static cursor.
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR STATIC FOR
SELECT EmpID,EmpName,Salary from ContractEmployee
OPEN cur_emp
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
--FETCH ABSOLUTE 3 FROM cur_emp INTO @Id,@name,@salary
WHILE @@Fetch_status = 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
--FETCH RELATIVE 3 FROM cur_emp INTO @Id,@name,@salary
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
END
CLOSE cur_emp
DEALLOCATE cur_emp
Contact Us if you have any questions on cursors.
We hope it helped you!! Keep Learning.
