Stored Procedure Problems

[DanTheMan]

Ok, here is the problem, this stored procedure is returning weird results. It will total the cart with tax sometimes, and then other times it won't. The biggest problem is the shipping, it isn't working right at all. It was working fine 3 days ago, although I would get an occasional error. Today it flat out stopped working. It is inputing 12.00 for shipping instead of 4.75. I am testing everything, but lately I have been inserting US for the country, and UT for the state. Also when I do the individual queries in the sql analyzer it returns everything just fine. I can't figure out where and why the results are being returned so different than my queries. I have spent at least 4 hours today looking it over trying everything I can think of. I am at a loss any help would be great.

 

 

/* DBTotalCart Totals the shopping cart */

 

CREATE PROCEDURE aaa_TestTotal

(

@UserID int,

@Group int,

@Active int,

@OrderID int,

@MasterID int

)

 

As

 

DECLARE @State varchar( 2 )

DECLARE @Country varchar( 2 )

DECLARE @Discount decimal( 3,2 )

DECLARE @OrderSubtotal money

DECLARE @OrderTax money

DECLARE @OrderShipping money

DECLARE @OrderDiscount money

DECLARE @ItemCount int

DECLARE @ChargeShipping int

 

SELECT

@State = o.ship_state,

@Country = o.ship_country

FROM SC_MasterOrders AS m, SC_Orders AS o

WHERE m.uid = @UserID

AND m.i_recid = o.moid

 

 

IF @Group <> 7

BEGIN

UPDATE SC_Items SET

discount = 0.00

WHERE oid = @OrderID

END

 

IF @Group = 7 AND @Active = 0

BEGIN

UPDATE SC_Items SET

discount = 0.00

WHERE oid = @OrderID

END

 

IF UPPER(@State) = 'UT'

BEGIN

 

SELECT

@ItemCount = SUM(i.quantity),

@OrderSubtotal = SUM(i.price * i.quantity),

@OrderTax = SUM(((i.price - i.discount) * i.quantity) * 0.0625),

@OrderDiscount = SUM(i.discount * i.quantity),

@ChargeShipping = SUM(p.charge_shipping)

FROM SC_Items AS i, CP_Inventory AS p

WHERE i.oid = @OrderID

AND i.pid = p.i_recid

 

END

ELSE

BEGIN

 

SELECT

@ItemCount = SUM(i.quantity),

@OrderSubtotal = SUM(i.price * i.quantity),

@OrderTax = 0.00,

@OrderDiscount = SUM(i.discount * i.quantity),

@ChargeShipping = SUM(p.charge_shipping)

FROM SC_Items AS i, CP_Inventory AS p

WHERE i.oid = @OrderID

AND i.pid = p.i_recid

 

END

 

 

IF @ChargeShipping > 0

BEGIN

 

IF UPPER(@Country) = 'US'

BEGIN

 

IF @OrderSubtotal > 50.00

SELECT @OrderShipping = 0.00

 

ELSE

SELECT @OrderShipping = 4.75

 

END

ELSE

BEGIN

 

IF UPPER(@Country) = 'CA'

BEGIN

 

IF @ItemCount < 4

SELECT @OrderShipping = 5.75

 

IF @ItemCount > 3 AND @ItemCount < 7

SELECT @OrderShipping = 7.00

 

IF @ItemCount > 6 AND @ItemCount < 10

SELECT @OrderShipping = 9.00

 

IF @ItemCount > 9

SELECT @OrderShipping = 11.00

 

END

 

IF UPPER(@Country) = 'MX'

BEGIN

 

IF @ItemCount < 4

SELECT @OrderShipping = 7.00

 

IF @ItemCount > 3 AND @ItemCount < 7

SELECT @OrderShipping = 9.00

 

IF @ItemCount > 6 AND @ItemCount < 10

SELECT @OrderShipping = 11.00

 

IF @ItemCount > 9

SELECT @OrderShipping = 15.00

 

END

 

IF UPPER(@Country) <> 'CA' AND UPPER(@Country) <> 'MX'

BEGIN

 

IF @ItemCount < 4

SELECT @OrderShipping = 12.00

 

IF @ItemCount > 3 AND @ItemCount < 7

SELECT @OrderShipping = 15.00

 

IF @ItemCount > 6 AND @ItemCount < 10

SELECT @OrderShipping = 17.00

 

IF @ItemCount > 9

SELECT @OrderShipping = 20.00

 

END

 

END

 

END

ELSE

BEGIN

 

SELECT @OrderShipping = 0.00

 

END

 

 

UPDATE SC_Items SET

moid = @MasterID

WHERE oid = @OrderID

 

UPDATE SC_Orders SET

subtotal = @OrderSubtotal,

tax = @OrderTax,

shipping = @OrderShipping,

total = (@OrderSubtotal + @OrderTax + @OrderShipping)

WHERE i_recid = @OrderID

 

UPDATE SC_MasterOrders SET

discount = @OrderDiscount,

subtotal = @OrderSubtotal,

tax = @OrderTax,

shipping = @OrderShipping,

total = ((@OrderSubtotal + @OrderTax + @OrderShipping) - @OrderDiscount)

WHERE i_recid = @MasterID

 

 

GO

[Nerseus]

If you have Visual Studio and SQL Server 2000 you can setup a DB project and step through the stored procedure. I quickly glanced at your code but it's too difficult to tell what's going on without knowing what the data looks like. Maybe you could try first trimming out the parts you know aren't running (or suspect aren't running), and throwing in some PRINT '1', PRINT '2' type of statements and run the proc in Query Analyzer to see where you're getting.

 

-Ner