Blog Detail

SQL SERVER: How to remove cursor
28 Dec 2009
Tejas Shah
Many times developer ask me that How can they remove Cursor?
  They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same. 
Please find this code to remove cursor with Table variable:
  
--declare table to keep records to be processed

DECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100))

--populate table variable with data that we want to process

INSERT INTO @Table(Column1, Column2)

SELECT    Column1, Column2

FROM    <Table>

WHERE    <Conditions>

--declare variables to process each record

DECLARE @inc INT, @cnt INT

--Assign increment counter

SELECT @inc = 1

--Get Number of records to be processed

SELECT @cnt = COUNT(*)

FROM @Table

WHILE @inc <= @cnt BEGIN

    --As we have AutoID declared as IDENTITY, it always get only one record.

    --Get values in Variable and process it as you want.

    SELECT    @Column1 = Column1,

            @Column2 = Column2

    FROM    @Table

    WHERE    AutoID = @inc        
    --do your calculation here

    ....

    ....

     --Select next record

     SET @inc = @inc = 1 

END
  
By this way, we can remove CURSOR by Table variable.
  
It is quite easy to implement.
  
One more benefit is: It will process one record at a time, so it locks only that record at a time.
  
Let me know if you have any questions.

Tags

T-SQL SQL Developer SQL Server 2005 Cursor Query Performance

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Read More

Categories

Recent Post