Solved

Help with my SQL Server Stored Procedure

Posted on 2011-02-28
10
316 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Analysis of table use 7 27
Trouble connecting to SqlServer database 4 31
SQL JOIN 6 31
BULK INSERT most recent CSV 19 18
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now