SQL Server Alternatives Cursor

Print 3 min read

31 Aug 2022

Advanced

48.2K Views

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

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

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

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