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:
DECLARE statements – Declare variables used in the code block
SET\SELECT statements – Initialize the variables to a specific value
DECLARE CURSOR statement – Populate the cursor with values that will be evaluated
OPEN statement – Open the cursor to begin data processing
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.
