i doing an online shop project which have an shoppingcart and it stored database.
and i have the situation like this.
the products have properties such as size, color . and customerscan buy a product with particular size or color. and i havethe shopping cart table structure and data like following
Id(primary key) CartId productId size color quantity
1 1 1 S red 10
2 1 1 S black 2
3 1 1 S blue 3
4 1 1 M red 5
5 1 1 L blue 2
all the data above is i image the customer may inputed. And myproblem is how to use an stored procedure to updata above record when a customer buy the same product which is one of the product fromabove(have same productId, size, color)
and i try to use the following code but it didn't work
<code>
create procedure shoppingcart_add_item
( @.cartId int,
@.productId int,
@.size nvarchar(20),
@.color nvarchar(20),
@.quantity int
)
AS
DECLARE @.countproduct
DECLARE @.oldsize
DECLARE @.oldcolor
select @.countproduct=count(productId) FROM shoppingcart WHERE productId=@.productId AND cartId=@.cartId
select @.oldsize=size,@.oldcolor=color FROM shoppingcart WHERE productId=@.productId
IF @.CountItems > 0 and @.oldsize = @.size and @.oldcolor = @.color
UPDATE
ShoppingCart
SET
Quantity = (@.Quantity + ShoppingCart.Quantity)
WHERE
ProductId = @.ProductId
AND
CartId = @.CartId
ELSE /* New entry for this Cart. Add a new record */
INSERT INTO ShoppingCart
(
CartId,
ProductId,
Quantity,
color,
size
)
VALUES
(
@.CartId,
@.ProductId,
@.Quantity,
@.size,
@.color
)
</CODE>
and the result from this stored procedure is not what i want, what itry to say is can i stored all the size and color in @.oldsize and@.oldcolor array. then loop through the array to get the one iwant??
somebody get any idea? or don't know what i am talking about?
sorry i asked a very stupid quesation, i just think too much, actually the solution is increditable simple, it just need alittle bit SQL can slove this(simple get the Id and update the newrecord depend on this Id)
i don't know why i am so stupid to make things complicated.
No comments:
Post a Comment