Alternative to Cursors

Example of Cursor Alternative

Suppose we have table "ProductSales" that stores the information about each product sales. Now we want to calculate the Total Sales Quantity and Amount of each and every product.

We can solve this problem by following three methods.

1.  CREATE TABLE ProductsSales
2. (
3.  ID int IDENTITY(1,1) NOT NULL,
4.  ProductID int NOT NULL,
5.  ProductName varchar(50) NOT NULL,
6.  Qty int NOT NULL,
7.  Amount decimal(10, 2) NOT NULL )
8. GO
9. SELECT * FROM ProductsSales
10. --We have the table with below data 

Problem solution methods

1.                 Using Cursor

1.   SET NOCOUNT ON

2.  DECLARE @ProductID INT

3.  DECLARE @ProductName VARCHAR(100)

4.  DECLARE @TotalQty INT

5.  DECLARE @Total INT

6.  DECLARE @TProductSales TABLE

7.  (

8.   SNo INT IDENTITY(1,1),

9.   ProductID INT,

10.ProductName VARCHAR(100),

11.TotalQty INT,

12.GrandTotal INT

13.)

14.--Declare Cursor

15.DECLARE Cur_Product CURSOR FOR SELECT DISTINCT ProductID FROM ProductsSales

16.--Open Cursor

17.OPEN Cur_Product

18.--Fetch Cursor

19.FETCH NEXT FROM Cur_Product INTO @ProductID

20.WHILE @@FETCH_STATUS = 0

21.BEGIN

22.SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID

23.SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID

24.INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)

25.FETCH NEXT FROM Cur_Product INTO @ProductID END

26.--Close and Deallocate Cursor

27.CLOSE Cur_Product

28.DEALLOCATE Cur_Product

29.--See Calculated data

30.SELECT * FROM @TProductSales 

2.                 Using Table Variable

1.   SET NOCOUNT ON

2.  DECLARE @ProductID INT

3.  DECLARE @ProductName VARCHAR(100)

4.  DECLARE @TotalQty INT

5.  DECLARE @Total INT

6.  DECLARE @i INT =1

7.  DECLARE @count INT

8.   --Declare Table variables for storing data

9.  DECLARE @TProduct TABLE ( SNo INT IDENTITY(1,1),

10.ProductID INT

11.)

12.DECLARE @TProductSales TABLE

13.(

14.SNo INT IDENTITY(1,1),

15.ProductID INT,

16.ProductName VARCHAR(100),

17.TotalQty INT,

18.GrandTotal INT

19.)

20.--Insert data to Table variable @Product

21.INSERT INTO @TProduct(ProductID)

22.SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC

23.-- Count number of rows

24.SELECT @count = COUNT(SNo) FROM @TProduct WHILE (@i <= @count)

25.BEGIN

26.SELECT @ProductID = ProductID FROM @TProduct WHERE SNo = @i

27.SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID

28.SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID

29.INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)

30.SELECT @i = @i + 1

31.END

32.--See Calculated data

33.SELECT * FROM @TProductSales 

3.                 Using Temporary Table

1.   SET NOCOUNT ON

2.  DECLARE @ProductID INT

3.  DECLARE @ProductName VARCHAR(100)

4.  DECLARE @TotalQty INT

5.  DECLARE @Total INT

6.  DECLARE @i INT =1

7.  DECLARE @count INT

8.  --Create Temporary Tables for storing data

9.  CREATE TABLE #TProduct ( SNo INT IDENTITY(1,1),

10.ProductID INT

11.)

12.CREATE TABLE #TProductSales

13.(

14.SNo INT IDENTITY(1,1),

15.ProductID INT, ProductName VARCHAR(100), TotalQty INT, GrandTotal INT )

16.--Insert data to temporary table #Product

17.INSERT INTO #TProduct(ProductID) SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC

18.SELECT @count = COUNT(SNo) FROM #TProduct

19.WHILE (@i <= @count)

20.BEGIN

21.SELECT @ProductID = ProductID FROM #TProduct WHERE SNo = @i

22.SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID

23.SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID

24.INSERT INTO #TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)

25.SELECT @i = @i + 1

26.END

27.--See Calculated data

28.SELECT * FROM #TProductSales

29.--Now Drop Temporary Tables

30.DROP TABLE #TProduct

31.DROP TABLE #TProductSales