05
DecAs 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
Read More Articles Related to SQL Server
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.
Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.