Help with my SQL Server Stored Procedure

I am attempting a simple solution to updating the inventory column in my Product db. Here is the stored procedure I wrote.  I am sure it is the SP that is the problem.  BTW, I am a novice in this area so would appreciate any other points of view.  Maybe the problem will jump right out at you but I am beating my head against a wall.


CREATE PROCEDURE UpdateInventory
(@OrderID int,
 @CartID char (36))
 AS 
DECLARE @Quantity INT 
SELECT @Quantity = ShoppingCart.Quantity
FROM ShoppingCart INNER JOIN Product
ON ShoppingCart.ProductID = Product.ProductID
WHERE ShoppingCart.CartID = @CartID
UPDATE Product SET Inventory = (Inventory - @Quantity)
WHERE ShoppingCart.ProductID = Product.ProductID

Open in new window

pathfinder8008Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rajeevnandanmishraConnect With a Mentor Commented:
Hi,

I think you have already corrected the code provided by lowfatspread. Otheriwise below is the corrected code. Earlier it was a little typo mistake.

CREATE PROCEDURE UpdateInventory
(@OrderID int,
 @CartID char (36))
 AS 

update p  
set inventory = p.inventory - b.qty
 from product as p, 
   (select productid
          ,sum(quantity) as qty
      from shoppingcart 
     where cartid=@cartid
     group by productid
    ) b  
 where p.productid=b.productid 

Return
go

Open in new window

0
 
Paul JacksonSoftware EngineerCommented:
try this :

CREATE PROCEDURE UpdateInventory 
(@OrderID int, 
 @CartID char (36)) 
 AS  
DECLARE @Quantity INT  
SELECT @Quantity = S.Quantity 
FROM ShoppingCart S 
WHERE S.CartID = @CartID 
UPDATE P 
SET P.Inventory = (P.Inventory - @Quantity) 
FROM Product P INNER JOIN ShoppingCart S
ON S.ProductID = P.ProductID 

Open in new window

0
 
Paul JacksonSoftware EngineerCommented:
Actually you don't even need the join :

CREATE PROCEDURE UpdateInventory  
(@OrderID int,  
 @CartID char (36))  
 AS   
DECLARE @Quantity INT   
SELECT @Quantity = S.Quantity  
FROM ShoppingCart S  
WHERE S.CartID = @CartID  

UPDATE P  
SET P.Inventory = (P.Inventory - @Quantity)  
FROM Product P 

Open in new window


0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Rajesh_mjCommented:
Please check below code:
CREATE PROCEDURE UpdateInventory
( -- @OrderID int, -- @OrderId has been removed becuase it is not using in this procedure
 @CartID char (36))
 AS 

-- If condition will ensure that enough products are avilable in the inventory
If not exists(select *  FROM ShoppingCart INNER JOIN Product
ON ShoppingCart.ProductID = Product.ProductID
where Inventory <  ShoppingCart.Quantity)
Begin
	Update product set Inventory = (Inventory -  ShoppingCart.Quantity) 
	FROM ShoppingCart INNER JOIN Product
	ON ShoppingCart.ProductID = Product.ProductID
	WHERE ShoppingCart.CartID = @CartID
End
/*
Else
Begin
  -- Add the code what to do if enough products not exists in the inventory
End
*/

Open in new window

0
 
LowfatspreadCommented:
more like this?

calculate the quantity of the products in the order and update the inventory table...

do it in AS FEW STATEMENTS AS POSSIBLE - ALWAYS AN IMPORTANT CONSIDERATION WITH DATABASE APPLICATIONS


CREATE PROCEDURE UpdateInventory
(@OrderID int,
 @CartID char (36))
 AS 

update product
  set inventory=inventory - x.qty
 from product as p
 inner join 
   (select cartid,productid
          ,sum(quantity) as qty
      from shoppingcart 
     group by cartid,productid
    ) as B 
 on p.productid=b.productid
 where b.cartid=@cartid

Return
go

Open in new window

0
 
rajeevnandanmishraCommented:
Hi,

Just to add on the comment#35004391.
It would be better to add the "where cartid = @cartid" condition within the inner query.
Also as it seems from the procedure, you may need to use the @OrderID also in your query to filter the correct data.

0
 
pathfinder8008Author Commented:
So much brilliant input. I thank you.

Now to process it. I have the responsibility of gleaning the best from all these good comments. I admire the approach in comment 35004391, thanks Lowfatspread. I might need to modify this to account for x.qty as I got the following:

The multi-part identifier "x.qty" could not be bound.

Please let me work with all the above comments for a day and probably will take something from each and accept multiple solutions.
0
 
LowfatspreadConnect With a Mentor Commented:
sorry the x.qty should have been b.qty


if your don't need the @orderid parameter...

then this may be better....
CREATE PROCEDURE UpdateInventory
(@OrderID int,
 @CartID char (36))
 AS 

update product
  set inventory=inventory - b.qty
 from product as p
 inner join 
   (select productid
          ,sum(quantity) as qty
      from shoppingcart 
     where b.cartid=@cartid
     group by productid
    ) as B 
 on p.productid=b.productid 

Return
go

Open in new window

0
 
pathfinder8008Author Commented:
Sorry Lowfatspread, the OrderID is an aberration.

But now I am getting

The multi-part identifier "b.cartid" could not be bound.
0
 
pathfinder8008Author Commented:
Thanks to our experts working together I found a solution that was elegant and performance oriented.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.