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
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
Hi shiva this is ok but i am unable to see the images could you please post those images for this post.
ReplyDelete