How to Avoid Cursors - Way to Avoid Cursors - SQL Server

How to Avoid Cursors - Way to Avoid Cursors

Many of us know already that cursor causes performance issues as the data traversed from cursor is stored in tempdb which means lots of Disk I/O is performed and would cause degraded performance.Now let us see how to eliminate cursors from my code.

First we create a sample table called as Employee

/* Creation of Employee Table Data */
CREATE TABLE Employee( Emp_ID INT IDENTITY(1,1),Emp_Name VARCHAR(100),Emp_City VARCHAR(50))

Secondly we insert sample data to the employee table

/* Insert Sample Data into Employee Table Data */
INSERT INTO Employee (Emp_Name,Emp_City)SELECT 'Emp1','MAS'
INSERT INTO Employee (Emp_Name,Emp_City)SELECT 'Emp2','HYD'
INSERT INTO Employee (Emp_Name,Emp_City)SELECT 'Emp3',NULL
INSERT INTO Employee (Emp_Name,Emp_City)SELECT 'Emp4','HYD'
INSERT INTO Employee (Emp_Name,Emp_City)SELECT 'Emp5','SBC'

To get employee data one by one in cursor we use the below code

/*Cursors Usage */
SET NOCOUNT ON

DECLARE @EMP_ID VARCHAR(50)
DECLARE @EMP_NAME VARCHAR(50)

DECLARE EMP_CUR CURSOR FOR
SELECT EMP_ID, EMP_NAME FROM Employee

OPEN EMP_CUR

FETCH NEXT FROM EMP_CUR INTO @EMP_ID ,@EMP_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @EMP_ID,@EMP_NAME

FETCH NEXT FROM EMP_CUR INTO @EMP_ID ,@EMP_NAME
END

CLOSE EMP_CUR
DEALLOCATE EMP_CUR

SET NOCOUNT OFF

Now lets see the way to avoid cursors using a simple while loop
/* Code to avoid cursor */
DECLARE @MinSlNo INT
DECLARE @MaxSlNo INT

DECLARE @TBL_EMP TABLE (SNo INT IDENTITY(1,1),
EMP_ID VARCHAR(50),
EMP_NAME VARCHAR(50))

INSERT INTO @TBL_EMP (EMP_ID,EMP_NAME)
SELECT EMP_ID,EMP_NAMEFROM Employee

SELECT @MinSlNo = 1
SELECT @MaxSlNo = MAX(SNo) FROM @TBL_EMP

WHILE @MinSlNo < = @MaxSlNo
BEGIN
SELECT EMP_ID,EMP_NAME
FROM @TBL_EMP
WHERE SNo = @MinSlNo

SELECT @MinSlNo = @MinSlNo + 1
END