Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

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

0
pathfinder8008
Asked:
pathfinder8008
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Paul JacksonCommented:
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 JacksonCommented:
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
LowfatspreadCommented:
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
 
rajeevnandanmishraCommented:
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
 
pathfinder8008Author Commented:
Thanks to our experts working together I found a solution that was elegant and performance oriented.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now