I have a table Laptops and with stored procedure I want calculate running total using INSERT AND UPDATE.
I'm trying to calculate running total on the following way.
ALTER PROCEDURE [dbo].[spInsert_Inventory]
(
@Brand Varchar(50),
@Series Varchar(50),
@Model Varchar(50),
@Ram int,
@HDD int,
@Qty int,
@Price int
)
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Laptops
WHERE Brand=@Brand
AND Series=@Series
AND Model=@Model
AND ram=@Ram
AND hdd=@HDD
)
BEGIN
DECLARE @totale int=0,@ID int
SELECT ID_Lap=@ID FROM Laptops
set @totale= (@Price)*(@Qty)
INSERT INTO Laptops VALUES (@Brand,@Series,@Model,@Ram,@HDD,isnull(@Qty,0),(isnull(@Price,0)),@totale)
END
ELSE
BEGIN
SET @totale = (SELECT SUM(@Price)*SUM(@Qty)FROM Laptops WHERE ID_Lap=@ID)
UPDATE Laptops set qty=isnull(@Qty,0)
, Price = (isnull(@Price,0)),Total=@totale
WHERE Brand=@Brand
AND Series=@Series
AND Model=@Model
AND ram=@Ram
AND hdd=@HDD
END
END
Now, when I try insert new record and calculate running total my result is fine but when using update running total is not change column Total.
What I have tried:
Desired output:
Brand Series Model Ram HDD Qty Price Total
AAA SAS DSS 200 25 3 3 9
BBB GFG KHH 50 65 5 20 100
AAA SAS DSS 200 25 22 3 75
BBB GFG KHH 50 65 5 10 150
BBB GFG KHH 50 65 2 4 158
AAA SAS DSS 200 25 2 5 85