Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server Cursor Alternatives

  Author : Shailendra Chauhan
Updated On : 24 Sep 2016
Total Views : 140,333   
Support : SQL Server 2005,2008,2012
 

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.

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

Problem solution methods

  1. Using Cursor

     SET NOCOUNT ON 
    DECLARE @ProductID INT 
    DECLARE @ProductName VARCHAR(100) 
    DECLARE @TotalQty INT 
    DECLARE @Total INT 
    DECLARE @TProductSales TABLE 
    ( 
     SNo INT IDENTITY(1,1),
     ProductID INT,
     ProductName VARCHAR(100),
     TotalQty INT,
     GrandTotal INT
     ) 
    --Declare Cursor 
    DECLARE Cur_Product CURSOR FOR SELECT DISTINCT ProductID FROM ProductsSales
     --Open Cursor 
    OPEN Cur_Product 
    --Fetch Cursor 
    FETCH NEXT FROM Cur_Product INTO @ProductID
    WHILE @@FETCH_STATUS = 0
     BEGIN 
    SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
    SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
    INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total) 
    FETCH NEXT FROM Cur_Product INTO @ProductID END 
    --Close and Deallocate Cursor
     CLOSE Cur_Product 
     DEALLOCATE Cur_Product
     --See Calculated data 
    SELECT * FROM @TProductSales 

  2. Using Table Variable

     SET NOCOUNT ON 
    DECLARE @ProductID INT 
    DECLARE @ProductName VARCHAR(100) 
    DECLARE @TotalQty INT 
    DECLARE @Total INT 
    DECLARE @i INT =1 
    DECLARE @count INT 
     --Declare Table variables for storing data 
    DECLARE @TProduct TABLE ( SNo INT IDENTITY(1,1),
     ProductID INT
     ) 
     DECLARE @TProductSales TABLE
     (
     SNo INT IDENTITY(1,1),
     ProductID INT,
     ProductName VARCHAR(100),
     TotalQty INT,
     GrandTotal INT
     ) 
     --Insert data to Table variable @Product 
     INSERT INTO @TProduct(ProductID)
     SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC 
     -- Count number of rows
     SELECT @count = COUNT(SNo) FROM @TProduct WHILE (@i <= @count)
     BEGIN
     SELECT @ProductID = ProductID FROM @TProduct WHERE SNo = @i
     SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
     SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
     INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)
     SELECT @i = @i + 1
     END
     --See Calculated data
     SELECT * FROM @TProductSales 

  3. Using Temporary Table

     SET NOCOUNT ON 
    DECLARE @ProductID INT 
    DECLARE @ProductName VARCHAR(100) 
    DECLARE @TotalQty INT 
    DECLARE @Total INT 
    DECLARE @i INT =1
    DECLARE @count INT 
    --Create Temporary Tables for storing data 
    CREATE TABLE #TProduct ( SNo INT IDENTITY(1,1),
     ProductID INT
     ) 
     CREATE TABLE #TProductSales
     (
     SNo INT IDENTITY(1,1),
     ProductID INT, ProductName VARCHAR(100), TotalQty INT, GrandTotal INT )
     --Insert data to temporary table #Product 
    INSERT INTO #TProduct(ProductID) SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC 
    SELECT @count = COUNT(SNo) FROM #TProduct 
    WHILE (@i <= @count) 
    BEGIN 
    SELECT @ProductID = ProductID FROM #TProduct WHERE SNo = @i
    SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID 
    SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID 
    INSERT INTO #TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)
    SELECT @i = @i + 1
     END
     --See Calculated data
     SELECT * FROM #TProductSales 
    --Now Drop Temporary Tables
     DROP TABLE #TProduct
     DROP TABLE #TProductSales 

Summary

In this article I try to explain the Cursor alternative in SQL Server with simple examples. I hope after reading this article you will be able to use Cursor alternative in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
11 DEC
ASP.NET MVC with AngularJS Development (offline)

Sat-Sun 09:30 AM-11:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri     6:30 AM-7:30 AM IST

3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (11:00 AM-12:30 PM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+