Detecting of record does not work in if query

Edit: Newer mind. I tested this query more after writing this, and now it seems to work!

I hope it continues to work.

In following query, else is never executed.

CREATE PROCEDURE Put_into_basket
( @.Product_code varchar(20))
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@.Product_code)
BEGIN
INSERT dbo.t_shopping_basket (Product_code, Name,Price)
SELECT Product_code, Name,Price
FROM dbo.t_product
WHERE Product_code= @.Product_code
END
ELSE --this part is never executed
BEGIN
UPDATE dbo.t_shopping_basket
SET Quantity=Quantity+1
WHERE Product_code=@.Product_code
END
END
GO

The query should test if there is a record or row with Product_code=@.Product_code.

If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of.

If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.

But when I test query, it never runs the quantity+1 part.

This is the code that I am using to do the same function. I use your method in other queries but also could not get it working for the cart. In my mind it was like the if exist can only take one other arguement either the update or the insert into but not both. So I changed mine around to this I also have to track another variable as there are different files they can access.

1SELECT2 @.CountItems = Count(ProductID)3FROM4 ShoppingCart5WHERE6 ProductID = @.ProductID7 AND8 CartID = @.CartID9 AND10Filenumber = @.FileNumber1112IF @.CountItems > 01314 UPDATE15 ShoppingCart16 SET17 Quantity = (@.Quantity + ShoppingCart.Quantity)18 WHERE19 ProductID = @.ProductID20 AND21 CartID = @.CartID22 AND23FileNumber = @.FileNumber2425ELSE26 INSERT INTO ShoppingCart27 (28 CartID,29 Quantity,30 ProductID,31FileNumber32 )33 VALUES34 (35 @.CartID,36 @.Quantity,37 @.ProductID,38@.FileNumber39 )

|||

Thanks, I'll keep this in mind. Expesially because my version didn't work at first. Hopefully it was my error and not something which may happen again.

I'm quite new at SQL, your code has some interesting features. Count(ProductID) for instance. You have all carts in same table?

Out of curiosity, where do need FileNumber field. That is, is specific to your solution?

Regards

Leif

|||

Yea it is specitic to my cart. They can buy the same report but covering different info. Not wanting to put a couple thousand individual prices in and having to update every time they added to the site, I have one price for each different report and then tell which report they bought. So the cart could show report1 several times and each one would have a different file number. If they buy something that is not related to a filenumber like a subscription then I just put a 0 in that field.

I use one table for the cart and if they buy, then I move the contents over to the order tables (2 tables) and delete the items from the cart. So that table should stay fairly small.

sql