• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

SQL Syntax Advice - Urgent If Possible

Hi all

I have the following SQL which basically transfers cart order data into the orders table but what I am finding is that it seems to process a user's cart that has several different products in it but as soon as you try processing a users cart that contains 2 of the same products, it returns the error even though the Product.Qty value is adequate to process the data:

Not all products are available in the Qty requested


Please could you provide advice as to where I could be going wrong with my syntax.

Many thanks,

Rit
CREATE PROCEDURE [dbo].[sproc_cart_Insert_Order] 
 
	@UserID Int,
	@TempID nvarchar(100)
 
AS
 
BEGIN TRANSACTION 
DECLARE @rInserted INT
DECLARE @rUpdated INT 
 
INSERT INTO dbo.OrderDetail
(
	ProductId, 
	Price, 
	Qty, 
	SetSize, 
	Colour, 
	Gather, 
	MaterialDrop, 
	StatusID, 
	Width, 
	Metrics,
	UserID,
	TempID,
	WhichRoom
)
SELECT     
	ProductID, 
	Price, 
	Qty, 
	SetSize, 
	Colour, 
	Gather, 
	MaterialDrop, 
	1, 
	Width, 
	Metrics, 
	@UserID,
	TempID,
	WhichRoom
 
FROM         dbo.CartItems
WHERE     (TempID = @TempID)
SET @rInserted = @@ROWCOUNT 
 
UPDATE p
SET 
	Qty = p.Qty - ci.Qty
FROM Product p JOIN Cartitems ci
	ON p.[Id] = ci.productid AND 
	p.Qty >= ci.Qty AND
	 ci.TempID = @TempID
 
 
SET @rUpdated = @@ROWCOUNT 
 
IF @rUpdated < @rInserted
BEGIN
  -- not all products had enough Qty!!
  ROLLBACK
  RAISERROR ( 'Not all products are available in the Qty requested', 16,2 ) WITH SETERROR
END
ELSE
BEGIN
  COMMIT
END
GO

Open in new window

0
rito1
Asked:
rito1
  • 5
  • 3
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
The problem is that your count of inserted row is one per line item in the cart, while the count for the update is returning the distinct Products.  So for a cart with 3 items 2 of which are the same product the insert gives you 3 and the update gives you 2.  I am trying to create a sample to fix the problem as I see that it does not appear to be setting the Qty correctly in the Product table either.  Will post a query when I get it working.
0
 
rito1Author Commented:
Thanks CGLuttrell, you help is very much appreciated!...

I removed the transaction part of the query and it processes the items fine but did notice that it only seems to deduct 1 from the product quantity rather than 2 [if 2 of the same products were being processed].

Its 12:23am here so head is becoming a little thick now :-(

Thanks again,

Rich
0
 
Chris LuttrellSenior Database ArchitectCommented:
try changing your update statement to this.
UPDATE p
SET 
	Qty = p.Qty - ci.Qty
FROM Product p JOIN (SELECT TempID, ProductId, SUM(Qty) Qty FROM Cartitems WHERE TempID = @TempID GROUP BY TempID, ProductId) ci
	ON p.[Id] = ci.productid AND 
	p.Qty >= ci.Qty 

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Chris LuttrellSenior Database ArchitectCommented:
Actually you can simplify it a little more, you do not need to group by TempID.
UPDATE p
SET 
	Qty = p.Qty - ci.Qty
FROM Product p JOIN (SELECT ProductId, SUM(Qty) Qty FROM Cartitems WHERE TempID = @TempID GROUP BY ProductId) ci
	ON p.[Id] = ci.productid AND 
	p.Qty >= ci.Qty 

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
You can leave your transaction and count check in but change it to see if the same number of products were updated as are DISTINCT in the cart like below.
CREATE PROCEDURE [dbo].[sproc_cart_Insert_Order] 
 
	@UserID Int,
	@TempID nvarchar(100)
 
AS
 
BEGIN TRANSACTION 
DECLARE @rInserted INT
DECLARE @distinctProducts INT
DECLARE @rUpdated INT 
 
SELECT @distinctProducts = COUNT(DISTINCT ProductId) FROM CartItems WHERE TempID = @TempID;
 
INSERT INTO dbo.OrderDetail
(
	ProductId, 
	Price, 
	Qty, 
	SetSize, 
	Colour, 
	Gather, 
	MaterialDrop, 
	StatusID, 
	Width, 
	Metrics,
	UserID,
	TempID,
	WhichRoom
)
SELECT     
	ProductID, 
	Price, 
	Qty, 
	SetSize, 
	Colour, 
	Gather, 
	MaterialDrop, 
	1, 
	Width, 
	Metrics, 
	@UserID,
	TempID,
	WhichRoom
 
FROM         dbo.CartItems
WHERE     (TempID = @TempID)
SET @rInserted = @@ROWCOUNT 
 
UPDATE p
SET 
	Qty = p.Qty - ci.Qty
FROM Product p JOIN (SELECT ProductId, SUM(Qty) Qty FROM Cartitems WHERE TempID = @TempID GROUP BY ProductId) ci
	ON p.[Id] = ci.productid AND 
	p.Qty >= ci.Qty 
 
 
SET @rUpdated = @@ROWCOUNT 
 
IF @rUpdated < @distinctProducts
BEGIN
  -- not all products had enough Qty!!
  ROLLBACK
  RAISERROR ( 'Not all products are available in the Qty requested', 16,2 ) WITH SETERROR
END
ELSE
BEGIN
  COMMIT
END
GO

Open in new window

0
 
rito1Author Commented:
Excellent, thanks CGLuttrell. that worked perfectly.

Rit
0
 
rito1Author Commented:
I really couldn't have asked for a better solution.

Many thanks!!
0
 
Chris LuttrellSenior Database ArchitectCommented:
Thanks for the feed back.  Glad I could help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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