Adding Product into cart using SP

Question

Adding Product into cart using SP

GA_SQL

Hall of Fame

Points: 3166
More actions
November 3, 2016 at 5:18 am

#314609

I have to Create a Store Procedure named AddToCart.This procedure should take CustomerId,
productId,ProductName and OrderId as input and add this to the order table. -- this one i have done below
You shall also be able to send NULL in the order table and a new order shall be is created.
If the product already exists then the product value should be incremented by 1(Please help me in achieving this)
I tried below code:
ALTER PROCEDURE [dbo].[AddToCart]
(@CustomerID char(36),
@ProductId int,
@Productname VARCHAR(100),
@OrderID varchar(1000))
AS
BEGIN
update quantity and if it is same cart info
Update Product
SET Quantity = Quantity + 1
Where ProductId = @ProductId ANd CustomerID = @CustomerID AND OrderID = @OrderID
Insert Into Product (CustomerID, ProductId, OrderID, Quantity, Date1)
SELECT @CustomerID, @ProductId, @OrderID, 1, GETDATE()
From Product Where ProductId = @ProductId And CustomerID = @CustomerID AND OrderID <> @OrderID
UNION
SELECT @CustomerID, @ProductId, @OrderID, 1, GETDATE()
From Product Where ProductId = @ProductId
END
Table Structure
CREATE TABLE [dbo].[Product](
[CustomerID] [int] NULL,
[ProductID] [int] NULL,
[ProductName] [varchar](100) NULL,
[OrderID] [varchar](100) NULL,
[Quantity] [int] NULL,
[Date1] [date] NULL
) ON [PRIMARY]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What does your procedure not do that you need it to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GA_SQL Hall of Fame Points: 3166 More actions · Answer 2

i expect the procedure to do following things

You shall also be able to send NULL in the order table and a new order shall be is created.

If the product already exists then the product value should be incremented by 1

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

And which of those is it not doing or doing incorrectly?

There's no sample data, so I can't test your procedure out.

btw, that's a very poor design for the table, it's mixing the orders table and the products table together and it's going to be hell to work with. I'd suggest you step back from the procedure for a bit and fix the table design.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

drew.allen SSC Guru Points: 77005 More actions · Answer 4

The problem is here:

Insert Into Product (CustomerID, ProductId, OrderID, Quantity, Date1)

SELECT @CustomerID, @ProductId, @OrderID, 1, GETDATE()

From Product Where ProductId = @ProductId And CustomerID = @CustomerID AND OrderID <> @OrderID

UNION

SELECT @CustomerID, @ProductId, @OrderID, 1, GETDATE()

From Product Where ProductId = @ProductId

The second part will add a record if anybody ever ordered that product including the current order if it already exists. The first part is more specific in that it will add a record if that customer has any other order for that product.

The first part is a subset of the second part, so if there are any records that match the first part, they will also match the second part.

The problem here is that you're trying to return a record to trigger the insert, when there will never be a record to return under the conditions where you need to trigger the insert.

But, as Gail said, your problems with the table structure are a more pressing concern.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA