Cursor and its implementation

Hello friends, today we are going to learn about Cursors and their implementation. So before we start with Cursor and its implementation, we should know why do we use Cursors?

Why do we use Cursors: - implementing Cursor is a mechanism through which we can loop through a result set returned by a select query. This is similar to record set used in VB and data reader in .Net, these two techniques are used by front end programmers to loop through on the result set. Similarly we have Cursor mechanism which is used to loop through on a result set by backend programmers.

Lets take an example of a result set to implement a Cursor.

Problem Statement:- I have a stored procedure which takes four parameters as input and calculate the Salary of an employee based on some logic. Now I want to execute this stored procedure to get salary of the all employees. I have employee table where all the details are there which are required to pass into stored procedure as an input. So this is a scenario where we can implement the Cursor. I will pass all the rows from the employee table to the Cursor to loop through each record and get the salary calculated for each employee.

Lets create employee table using below script and insert few rows into it.

Create table Employee(Eid int,Name varchar(50),Basic_Sal int,HRA int)

Insert into Employee values(101,'Neeraj',50000,10000)

Insert into Employee values(102,'Ram',30000,5000)

Insert into Employee values(103,'Rachit',24000,4900)

Insert into Employee values(104,'Akshay',49000,23000)

Insert into Employee values(105,'Ankit',38000,4300)     

          

Now we will create the stored procedure which will calculate the Salary of the employees. We will create the stored procedure using below SQL script.

Create procedure Usp_Calculate_Salary(@Eid int,@Basic_Sal int,@HRA int,@Name Varchar(50))

as

begin

      Declare @sal int

      set @sal=@Basic_Sal +@Hra

      select @Eid,@Name,@sal

end

The above stored procedure will return salary of one employee, I want to get the salary of all employees in one go, hence I will loop through on Employee table and insert salary detail in a temporary table. Lets execute below SQL script which is implementing a Cursor on result set returned by select query on Employee table.

Create table #tmp(Eid int,Name varchar(50),Salary int)

declare Cur_Employee Cursor for Select Eid,Name,Basic_Sal,HRA from Employee

declare @Name varchar(50)

declare @Basic_Sal int

declare @HRA int

declare @Eid int

open Cur_employee

fetch next from Cur_employee into @Eid,@Name,@Basic_Sal,@HRA

While @@FETCH_STATUS =0

begin

     insert into #tmp execute Usp_Calculate_Salary @Eid,@basic_Sal,@HRA,@Name

     fetch next from Cur_employee into @Eid,@Name,@Basic_Sal,@HRA

end

select * from #tmp

Close Cur_Employee

deallocate Cur_Employee

Let’s understand the above SQL statements one by one.

Create table #tmp(Eid int,Name varchar(50),Salary int) àAll above variables will be used to hold the values of the columns which are being returned by the Cursor.

declare Cur_Employee Cursor for Select Eid,Name,Basic_Sal,HRA from Employee à This statement is being used for Cursor declaration where “Cur_Employee” is the name of Cursor and there is a select statement which tells us about the result set on which Cursor will be executed.

declare @Name varchar(50)

declare @Basic_Sal int

declare @HRA int

declare @Eid int

All above variables will be used to hold the values of the columns which are being returned by the Cursor.

open Cur_employeeà We are opening the Cursor  using this statement.

fetch next from Cur_employee into @Eid,@Name,@Basic_Sal,@HRAà This statement is fetching the first record from the Cursor object.

After fetching the first record from the Cursor object , we have applied while loop  and checking the global variable value @@FETCH_STATUS, this loop will continue until we receive a value other than 0. If @@FETCH_STATUS=0, it means that Cursor has returned a row.

While @@FETCH_STATUS =0

begin

     insert into #tmp execute Usp_Calculate_Salary @Eid,@basic_Sal,@HRA,@Name

     fetch next from Cur_employee into @Eid,@Name,@Basic_Sal,@HRA

end

In the body of while loop, we are executing the Stored procedure “Usp_Calculate_Salary” and storing the result into a temporary table.

Next statement in the body of while loop has been already explained.

select * from #tmp à After end of while loop, we are selecting all the rows from the temporary table.

Close Cur_Employeeà Closing the Cursor.

deallocate Cur_Employeeà Deallocating the memory held by Cursor.

Lets execute the Batch of SQL Statement and see the results.

There are lots of thing to cover related to Cursors, which I have not explained in this article. I will cover those topics in my forthcoming articles.