Split shoppingcart items - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split shoppingcart items

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 08:49:00

Hi, I asked a question to the developers of a webshop application because right now when somebody adds items to their cart they will be added like so:

Product quantity price
ASDFG 2 40
12345 3 20
----------------------------------------
TOTAL 5 140

But I want them to appear like this:

Product quantity price
ASDFG 1 40
ASDFG 1 40
12345 1 20
12345 1 20
12345 1 20
----------------------------------------
Total 5 140

They told me I had to change the SP, but I don;t know how.
The original is here:

USE [printvisie]
GO
/****** Object: StoredProcedure [dbo].[CAT_ShoppingCartAddItem] Script Date: 11/07/2008 14:44:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[CAT_ShoppingCartAddItem]
(
@CartID nvarchar(50),
@ProductID int,
@Quantity int,
@UnitCost money,
@Options nvarchar(2000),
@AllocDate datetime,
@InventoryCheck bit,
@TestMode bit = Null,
@SKU nvarchar(100),
@BillingPeriod int,
@CartPriceRuleID int,
@CouponCodeID int
)
As

Declare @RecordID int
Declare @Status int

Select @Status = Status from CAT_Products Where ProductID = @ProductID

if @InventoryCheck = 0 OR @Status = 3 OR @Status = 5
begin

SELECT
TOP 1 @RecordID = RecordID
FROM
CAT_ShoppingCart
WHERE
ProductID = @ProductID
AND
CartID = @CartID
AND
Options = @Options
AND
(AllocDate = @AllocDate or (@AllocDate is Null and AllocDate is Null))
AND
SKU = @SKU
AND
ConfigCompleted is Null /*Use BaseRecID is Null to replace all ConfigCompleted is Null and UnitCost is not Null checks to determine config items, ConfigCompleted is not null after configuration has been completed and UnitCost is Null before configuration has been completed, BaseRecID is not null before and after this for all config items */
AND
BillingPeriod = @BillingPeriod
AND
UnitCost is not Null
AND
CartPriceRuleID is Null

IF @RecordID is Not Null /* There are items - update the current quantity */
begin
UPDATE
CAT_ShoppingCart
SET
Quantity = (@Quantity + Quantity),
UnitCost = @UnitCost
WHERE
RecordID = @RecordID

end
ELSE /* New entry for this Cart. Add a new record */
begin

INSERT INTO CAT_ShoppingCart
(
CartID,
Quantity,
ProductID,
UnitCost,
Options,
AllocDate,
SKU,
BillingPeriod,
CartPriceRuleID,
CouponCodeID,
Tax
)
VALUES
(
@CartID,
@Quantity,
@ProductID,
@UnitCost,
@Options,
@AllocDate,
@SKU,
@BillingPeriod,
@CartPriceRuleID,
@CouponCodeID,
0
)

end

end
else
begin

Declare @AvailableQuant int
Declare @QuantityFromCart int
set @QuantityFromCart = 0

Declare @POSelID nvarchar(2000)

if @Options <> ''
Begin

Declare @nNdx int

Set @nNdx = CHARINDEX(': p', @Options)

Set @POSelID = SUBSTRING(@Options, @nNdx + 2, CHARINDEX('<br>', @Options) - 2 - @nNdx)

End

Select @AvailableQuant = POSelStock from CAT_ProdOptionsSelections where POSelID = @POSelID

If @AvailableQuant is Null
Begin

Select @AvailableQuant = Stock from CAT_Products Where ProductID = @ProductID

Select @QuantityFromCart = sum(Quantity) from CAT_ShoppingCart where ProductID = @ProductID AND CartID = @CartID AND UnitCost is not Null

End
Else
Begin

Select @QuantityFromCart = sum(Quantity) from CAT_ShoppingCart where ProductID = @ProductID AND CartID = @CartID AND UnitCost is not Null AND Options = @Options

End

If @Quantity > (@AvailableQuant - isnull(@QuantityFromCart,0))
Begin
set @Quantity = (@AvailableQuant - isnull(@QuantityFromCart,0))
if @Quantity > 0
Begin
SELECT [ProductName] + '; ' + Cast(@Quantity as nvarchar) As [message] From CAT_Products Where ProductID = @ProductID
End
Else
Begin
SELECT [ProductName] + '; ' + '0' As [message] From CAT_Products Where ProductID = @ProductID
End
End

if @Quantity > 0 and IsNull(@TestMode,0) = 0
Begin

SELECT
TOP 1 @RecordID = RecordID
FROM
CAT_ShoppingCart
WHERE
ProductID = @ProductID
AND
CartID = @CartID
AND
Options = @Options
AND
SKU = @SKU
AND
ConfigCompleted is Null /*use BaseRecID is Null to replace all configcompleted UnitCost is not Null checks */
AND
BillingPeriod = @BillingPeriod
AND
UnitCost is not Null
AND
CartPriceRuleID is Null

IF @RecordID is Not Null /* There are items - update the current quantity */
Begin

UPDATE
CAT_ShoppingCart
SET
Quantity = (@Quantity + Quantity),
UnitCost = @UnitCost
WHERE
RecordID = @RecordID
End

ELSE /* New entry for this Cart. Add a new record */

Begin

INSERT INTO CAT_ShoppingCart
(
CartID,
Quantity,
ProductID,
UnitCost,
Options,
AllocDate,
SKU,
BillingPeriod,
CartPriceRuleID,
CouponCodeID,
Tax
)
VALUES
(
@CartID,
@Quantity,
@ProductID,
@UnitCost,
@Options,
@AllocDate,
@SKU,
@BillingPeriod,
@CartPriceRuleID,
@CouponCodeID,
0
)

End

End

End

So can someone help me?????? (please)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-07 : 08:56:08

declare @t table(Product varchar(10),quantity int,price int)
insert into @t
select 'ASDFG', 2, 40 union all
select '12345', 3, 20

select * from @t

select t1.product,1 as quantity, t1.price from @t as t1
inner join master..spt_values as t2 on t1.quantity>t2.number
where t2.type='p'

Madhivanan

Failing to plan is Planning to fail

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 08:58:17

So you're suggesting to replace the selct SP and not the insert?????

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 09:18:27

I mean, how would I change this in the SP?

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 09:26:14

the inserts should be modified as below i guess

INSERT INTO CAT_ShoppingCart
(
CartID,
Quantity,
ProductID,
UnitCost,
Options,
AllocDate,
SKU,
BillingPeriod,
CartPriceRuleID,
CouponCodeID,
Tax 
)
SELECT
@CartID,
1,
@ProductID,
@UnitCost, 
@Options,
@AllocDate,
@SKU,
@BillingPeriod,
@CartPriceRuleID,
@CouponCodeID,
0
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND @Quantity

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 09:35:01

So something like:

USE [printvisie]
GO

/****** Object: StoredProcedure [dbo].[CAT_ShoppingCartAddItem] Script Date: 11/07/2008 14:44:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[CAT_ShoppingCartAddItem]
(
@CartID nvarchar(50),
@ProductID int,
@Quantity int,
@UnitCost money,
@Options nvarchar(2000),
@AllocDate datetime,
@InventoryCheck bit,
@TestMode bit = Null,
@SKU nvarchar(100),
@BillingPeriod int,
@CartPriceRuleID int,
@CouponCodeID int
)
As

Declare @RecordID int
Declare @Status int

Select @Status = Status from CAT_Products Where ProductID = @ProductID

declare @t table(Product varchar(10),quantity int,price int)
insert into @t

something, something, something????

if @InventoryCheck = 0 OR @Status = 3 OR @Status = 5
begin

SELECT
TOP 1 @RecordID = RecordID
FROM
CAT_ShoppingCart
WHERE
ProductID = @ProductID
AND
CartID = @CartID
AND
Options = @Options
AND
(AllocDate = @AllocDate or (@AllocDate is Null and AllocDate is Null))
AND
SKU = @SKU
AND
ConfigCompleted is Null /*Use BaseRecID is Null to replace all ConfigCompleted is Null and UnitCost is not Null checks to determine config items, ConfigCompleted is not null after configuration has been completed and UnitCost is Null before configuration has been completed, BaseRecID is not null before and after this for all config items */
AND
BillingPeriod = @BillingPeriod
AND
UnitCost is not Null
AND
CartPriceRuleID is Null

IF @RecordID is Not Null /* There are items - update the current quantity */
begin
UPDATE
CAT_ShoppingCart
SET
Quantity = (@Quantity + Quantity),
UnitCost = @UnitCost
WHERE
RecordID = @RecordID

end
ELSE /* New entry for this Cart. Add a new record */
begin

INSERT INTO CAT_ShoppingCart
(
CartID,
Quantity,
ProductID,
UnitCost,
Options,
AllocDate,
SKU,
BillingPeriod,
CartPriceRuleID,
CouponCodeID,
Tax
)
SELECT
@CartID,
1,
@ProductID,
@UnitCost,
@Options,
@AllocDate,
@SKU,
@BillingPeriod,
@CartPriceRuleID,
@CouponCodeID,
0
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND @Quantity

end

end
else
begin

Declare @AvailableQuant int
Declare @QuantityFromCart int
set @QuantityFromCart = 0

Declare @POSelID nvarchar(2000)

if @Options <> ''
Begin

Declare @nNdx int

Set @nNdx = CHARINDEX(': p', @Options)

Set @POSelID = SUBSTRING(@Options, @nNdx + 2, CHARINDEX('<br>', @Options) - 2 - @nNdx)

End

Select @AvailableQuant = POSelStock from CAT_ProdOptionsSelections where POSelID = @POSelID

If @AvailableQuant is Null
Begin

Select @AvailableQuant = Stock from CAT_Products Where ProductID = @ProductID

Select @QuantityFromCart = sum(Quantity) from CAT_ShoppingCart where ProductID = @ProductID AND CartID = @CartID AND UnitCost is not Null

End
Else
Begin

Select @QuantityFromCart = sum(Quantity) from CAT_ShoppingCart where ProductID = @ProductID AND CartID = @CartID AND UnitCost is not Null AND Options = @Options

End

If @Quantity > (@AvailableQuant - isnull(@QuantityFromCart,0))
Begin
set @Quantity = (@AvailableQuant - isnull(@QuantityFromCart,0))
if @Quantity > 0
Begin
SELECT [ProductName] + '; ' + Cast(@Quantity as nvarchar) As [message] From CAT_Products Where ProductID = @ProductID
End
Else
Begin
SELECT [ProductName] + '; ' + '0' As [message] From CAT_Products Where ProductID = @ProductID
End
End

if @Quantity > 0 and IsNull(@TestMode,0) = 0
Begin

SELECT
TOP 1 @RecordID = RecordID
FROM
CAT_ShoppingCart
WHERE
ProductID = @ProductID
AND
CartID = @CartID
AND
Options = @Options
AND
SKU = @SKU
AND
ConfigCompleted is Null /*use BaseRecID is Null to replace all configcompleted UnitCost is not Null checks */
AND
BillingPeriod = @BillingPeriod
AND
UnitCost is not Null
AND
CartPriceRuleID is Null

IF @RecordID is Not Null /* There are items - update the current quantity */
Begin

UPDATE
CAT_ShoppingCart
SET
Quantity = (@Quantity + Quantity),
UnitCost = @UnitCost
WHERE
RecordID = @RecordID
End

ELSE /* New entry for this Cart. Add a new record */

Begin

INSERT INTO CAT_ShoppingCart
(
CartID,
Quantity,
ProductID,
UnitCost,
Options,
AllocDate,
SKU,
BillingPeriod,
CartPriceRuleID,
CouponCodeID,
Tax
)
SELECT
@CartID,
1,
@ProductID,
@UnitCost,
@Options,
@AllocDate,
@SKU,
@BillingPeriod,
@CartPriceRuleID,
@CouponCodeID,
0
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND @Quantity

End

End

End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 09:39:26

I think so (to be honest i've not looked into other parts as question wason insertion of values, Did you require record to be splitted for updates? like if record with qty 1 exists and you're adding a new one with Qty 2 then should it insert two more records with Qty 1 and make total records 3?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 09:44:49

It should make a seperate record for each item.
So if someone orders 3 items, there should be 3 records I guess...

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 10:05:11

then whats the purpose of updates? in any case you need to insert records as 1 per item isnt it?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 10:12:37

Yes, perhaps if people set the quantity to 0 it still has a purpose.

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 10:16:26

in that case shouldnt you be deleting item? A item with 0 quantity has no meaning in shopping cart.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 10:18:15

I know, but I didn't make the application, I just want to get the SP changed.

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 10:27:18

but without knowing your business rules, how will you change it? I cant suggest anything unless i know what your rules are.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-07 : 10:30:28

Well, it's like I said in the first post, the only thing that needs to change is that the SP will not enter 1 record for whatever quantity, but it must insert a seperate record for each item.
No matter if it is an inser or an update I guess...

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 10:48:45

if its only inserts that matter, then my suggestion will be enough.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources