Showing posts with label Table Variable. Show all posts
Showing posts with label Table Variable. Show all posts

December 2, 2009

SQL SERVER: How to remove cursor

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.