Solved

Help with my SQL Server Stored Procedure

Posted on 2011-02-28
10
329 Views
Last Modified: 2012-05-11
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
Comment
Question by:pathfinder8008
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35003001
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
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35003293
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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 35003812
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35004391
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 35007027
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
 

Author Comment

by:pathfinder8008
ID: 35017320
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 125 total points
ID: 35017961
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
 

Author Comment

by:pathfinder8008
ID: 35018308
Sorry Lowfatspread, the OrderID is an aberration.

But now I am getting

The multi-part identifier "b.cartid" could not be bound.
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 125 total points
ID: 35026201
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
 

Author Closing Comment

by:pathfinder8008
ID: 35037585
Thanks to our experts working together I found a solution that was elegant and performance oriented.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question