Cursors in SQL Server

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.

 

Leave a Reply

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