Thursday 11 October 2012

How to make SQL Server Cursor Alternatives Using Table Variable

As we know, the cursors are required when we need to update records in a database table in singleton fashion means row by row. A Cursor also impacts the performance of the SQL Server since it uses the SQL Server instance’s memory, reduce concurrency, decrease network bandwidth and lock resources. 

You should avoid the use of cursor. In this article, I am explaining how you can use cursor alternatives like as WHILE loop, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

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. ) --Declare Cursor DECLARE Cur_Product CURSOR
    14. FOR SELECT DISTINCT ProductID FROM ProductsSales
    15. --Open Cursor
    16. OPEN Cur_Product
    17. --Fetch Cursor
    18. FETCH NEXT FROM Cur_Product INTO @ProductID
    19. WHILE @@FETCH_STATUS = 0
    20. BEGIN
    21. SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID
    22. SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID
    23. INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)
    24. FETCH NEXT FROM Cur_Product INTO @ProductID END
    25. --Close and Deallocate Cursor
    26. CLOSE Cur_Product
    27. DEALLOCATE Cur_Product
    28. --See Calculated data
    29. 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 
    32.  
    33.  
    34.  

No comments :