Solved

Help with my SQL Server Stored Procedure

Posted on 2011-02-28
10
323 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

770 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