Alteration of CURSOR

As we all know that the cursor is a bad performer. We always try to avoid cursor.

So the question is how we avoided the cursor? This article give us the demonstration, how to avoid the cursor. 

In this example i am trying to make a stored procedure for Calculate the total quantity and sales of the product in product wise. It will calculate for all the products  in Product Master table.

  

First example is using CURSOR

IF OBJECT_ID('Pr_ProdSalesRpt','p') IS NOT NULL

BEGIN

   DROP PROCEDURE Pr_ProdSalesRpt

END

GO

CREATE PROCEDURE Pr_ProdSalesRpt

AS

BEGIN

    SET NOCOUNT ON

    DECLARE my_cursor CURSOR

            FOR SELECT pID

                FROM   m_Products

           

    DECLARE @pID    INT

    DECLARE @pName  VARCHAR(100)

    DECLARE @topQty INT

    DECLARE @tot    MONEY

  

    DECLARE @prod_Sale TABLE

                  (

                        srlId     INT IDENTITY(1,1),

                      iProdID   INT,

                      vProdName VARCHAR(100),

                      iTotQty   INT,

                      iGrandTot MONEY

                  )

    OPEN my_cursor

 

    FETCH NEXT FROM my_cursor INTO @pID

  

    WHILE @@FETCH_STATUS = 0

            BEGIN

               SELECT @pName = prod_name

               FROM   m_Products

               WHERE  pID = @pID

              

               SELECT @topQty = SUM(qty),

                      @tot    = SUM(unit_Price*qty)

               FROM   tran_Orderdetail  

               WHERE  pID = @pID

              

               INSERT INTO  @prod_Sale

                           (iProdID,vProdName,iTotQty,iGrandTot)

               VALUES(@pID,@pName,@topQty,@tot)

           

               FETCH NEXT FROM my_cursor INTO @pID

            END

           

     CLOSE my_cursor

     DEALLOCATE my_cursor

 

     SELECT * FROM @prod_Sale

END

GO

EXEC Pr_ProdSalesRpt

Now I am trying to convert it without cursor.

It is quite simple and just needed some programmatic technique to control loop… etc.

Example to convert the stored procedure WITHOUT using CURSOR

IF OBJECT_ID('Pr_ProdSalesRpt','p') IS NOT NULL

BEGIN

   DROP PROCEDURE Pr_ProdSalesRpt

END

GO

CREATE PROCEDURE Pr_ProdSalesRpt

AS

BEGIN

      SET NOCOUNT ON

    SET XACT_ABORT ON

    DECLARE @pID       INT

    DECLARE @pName     VARCHAR(100)

    DECLARE @topQty    INT

    DECLARE @tot       MONEY

    DECLARE @Index     INT

    DECLARE @RecordCnt INT

  

    DECLARE @ProdID TABLE

            (

              srlId   INT IDENTITY(1,1),

              iProdID INT

            )

    DECLARE @prod_Sale TABLE

                  (

                        srlId      INT IDENTITY(1,1),

                      iProdID    INT,

                      vProdName  VARCHAR(100),

                      iTotQty    INT,

                      iGrandTot  MONEY

                  )

  

    SELECT @Index = 1

    INSERT INTO @ProdID

               (iProdID)

    SELECT   pID

    FROM     m_Products

    ORDER BY pID ASC

  

    SELECT @RecordCnt = COUNT(srlId)

    FROM   @ProdID

  

    WHILE (@Index <= @RecordCnt)

            BEGIN

                     SELECT @pID = iProdID

                     FROM   @ProdID

                     WHERE  srlId = @Index

                    

                     SELECT @pName = prod_name

                     FROM   m_Products

                     WHERE  pID = @pID

                    

                     SELECT @topQty = SUM(Quantity),

                            @tot    = SUM(UnitPrice*Quantity)

                     FROM   tran_Orderdetail

                     WHERE  pID = @pID

                    

                     INSERT INTO  @prod_Sale

                       (iProdID,vProdName,iTotQty,iGrandTot)

                     VALUES(@pID, @pName, @topQty, @tot)

                    

                     SELECT @Index = @Index + 1

            END

           

     SELECT * FROM @prod_Sale

END

GO

I think the construction of without CURASOR is quite easy and it definitely improve the performance.

Hope you like it.

Posted by: MR. JOYDEEP DAS