Alternate for Cursors in SQL Server

T-SQL provides us with a CURSOR statement to work on the data on a row-by-row basis. Cursors are especially handy when you are using OUTPUT stored procedures and need to pass one ID to the stored procedure at a time. However, this kills the whole theory of set-based operations, which are inherently faster than their row based counterparts. The following is one way of converting your CURSOR statement using the WHILE keyword in such a scenario

Suppose you have the following SQL statement:

DECLARE @item_category_id INT
        DECLARE @order_id INT
        DECLARE @purchase_order_id INT
        
        DECLARE item_cursor CURSOR FAST_FORWARD FOR
        
        SELECT
            it.item_category_id
            ,ord.order_id
        FROM dbo.item_categories it
        
        INNER JOIN dbo.orders ord
        ON ord.item_category_id = it.item_category_id
        
        WHERE ord.order_date >= '1-sep-05' 
        and it.isSuspended != 1 
        
        OPEN item_cursor
        
        FETCH NEXT FROM item_cursor INTO
        @item_category_id
        ,@order_id
        
        WHILE @@FETCH_STATUS = 0
            BEGIN
                    EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT
                    
                    /*
                        Call other code here to process your purchase order for this item
                    */
                    
                    FETCH NEXT FROM item_cursor INTO
                    @item-category_id
                    ,@order_id
            END
    

Now here is the code, which does not use a CURSOR but achieves the same result using a WHILE loop. The key here is to get all the item categories which fit our WHERE clause into a memory table and use the Primary Key of this table to pick up each successive item category.

--Declare variables
        DECLARE @item_category_id INT
        DECLARE @order_id INT
        DECLARE @purchase_order_id INT
        
        --Declare a memory table
        DECLARE @item_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL, --THE IDENTITY STATEMENT IS IMPORTANT!
                                    item_category_id INT,
                                    order_id INT
                                    )
                                    
        --now populate this table with the required item category values
        INSERT INTO @item_table
        SELECT  -- Same SELECT statement as that for the CURSOR
            it.item_category_id
            ,ord.order_id
        FROM dbo.item_categories it
        
        INNER JOIN dbo.orders ord
        ON ord.item_category_id = it.item_category_id
        
        WHERE ord.order_date >= '1-sep-05' 
        and it.isSuspended != 1                       
    
        DECLARE @item_category_counter INT
        DECLARE @loop_counter INT
        
        SET @loop_counter = ISNULL(SELECT COUNT(*) FROM @item_table),0) -- Set the @loop_counter to the total number of rows in the 
                                                                        -- memory table
                                                                      
        SET @item_category_counter = 1
        
        WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
            BEGIN
                SELECT @item_category_id = item_category_id
                       ,@order_id = order_id
                FROM @item_table 
                WHERE primary_key = @item_category_counter
                
                --Now pass the item-category_id and order_id to the OUTPUT stored procedure
                 EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT
                    
                /*
                    Call other code here to process your pruchase order for this item
                */
                
               SET @item_category_counter = @item_category_counter + 1
            END
    

This gives about 75 % performance increase