We generally have resultset of our select statements. Some times we want to perform any logic on the each row of the resultset. In these kind of scenario where we want to process each row and perform any logic on the row we make use of Cursor. A Cursor is always associated with SQL Select statement and will process each row from the resultant resultset.

Syntax

--Declare Variables
DECLARE @ID int
DECLARE @Description nvarchar(100)

--Declare Cursor 
DECLARE PRODUCTCURSOR CURSOR FOR 
SELECT ID,[Description] FROM Product WHERE ID < 2000

--With Open command cursor will point towards first row of result set
OPEN PRODUCTCURSOR 


--Cursor will retrieve first row and set to @Id and @description
FETCH NEXT FROM PRODUCTCURSOR INTO @ID,@Description

--@@FETCH_STATUS global variable to state whether cursor contains rows in the resultset or no
--When all records are looped it will will return -1

WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT ('ID= ' +CAST(@ID AS NVARCHAR(40)) +' Description ='+@Description)

--Cursor will retrieve next row and set to @Id and @description
FETCH NEXT FROM PRODUCTCURSOR INTO @ID,@Description
END

--Close the Cursor
CLOSE PRODUCTCURSOR

--DeAllocate Cursor 
DEALLOCATE PRODUCTCURSOR 

Consider that we have a below table with 450002 number of records with below structure.

Now business team recognized that there are some customers which has wrong Account Numbers updated in the table corresponding to Pan number and they want this to be rectified, they share the xls file with the Pan Number and there respective Account details as shown below:

So now in order to update these AccountNumbers i first store them on database by uploading via excel import in sql server.

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Edit the table Name if you want to give new name or use the Sheet as given by SQL server. Click on edit mapping if you want to change the data type of column.

Step 8

Click on Next

Next

Status of Import

Now in order to update the same we will make use of Cursor to update these 890 records. We will use Cursor for the same.

In order to do the same i have created belo cursor which will update the records of the corresponding records present in Sheet table which we imported from excel.

--Declare Variables
DECLARE @PanNumber	nvarchar(20)
DECLARE @AccountNumber nvarchar(20)
begin transaction

declare @ToUpdateAccountNumber  nvarchar(20)
DECLARE @ToUpdatePanNumber nvarchar(20)
--Declare Cursor 
DECLARE UpdateCustomerCURSOR CURSOR FOR 
SELECT [Pan Number],[Account number] FROM  [dbo].[Sheet1$]

OPEN UpdateCustomerCURSOR 

FETCH NEXT FROM UpdateCustomerCURSOR  INTO @PanNumber,@AccountNumber

WHILE(@@FETCH_STATUS=0)
BEGIN

update mstCustomer set AccountNumber=@AccountNumber where PanNumber=@PanNumber

--Cursor will retrieve next row and set to @Id and @description
FETCH NEXT FROM UpdateCustomerCURSOR INTO @PanNumber,@AccountNumber
END

--Close the Cursor
CLOSE UpdateCustomerCURSOR

--DeAllocate Cursor 
DEALLOCATE UpdateCustomerCURSOR 

So now when we execute the below block to update the records as shown below

We can see it took 1 min 18 second to complete the update process, we can say it as time consuming query. Generally it’s said to avoid using cursor due to it’s performance issues as we just saw in above figure. So in order to get the performance benefits generally i solve this situation using Joins.

update mstCustomer set AccountNumber=[dbo].[Sheet1$].[Account number] 
from mstCustomer
inner join [dbo].[Sheet1$] on
mstCustomer.PanNumber= [dbo].[Sheet1$].[Pan Number]

We can clearly see the performance we get threw join. The same query where took minutes whereas over it took only 6 seconds to perform the same operation. There is a drastic improvement of performace when we used join over cursor.