July 6, 2011 at 2:59 pm
Below is a little sproc that I have with-in my get_ShippingCost sproc. What I need to do is add a crate cost to the over all shipping cost. My vendor is charging us a crate cost for up to five doors. For a single door there is one door, and for double doors there are two doors per unit.
I am setting the @SingleDoorCount and the @DoubleDoorCount higher up in the sproc. For some reason I cannot get the SET @LeafCount to actually be set. I know the IF statements are evaluating correctly because i can pre-set the @LeafCount INT =1 and everything is fine. What do I need to do to get the SET @LeafCount = @SingleDoorCount + (@DoubleDoorCount * 2); to work correctly?
Second question: Is there a better piece of code that i can use other than the IF(@LeafCount < 6) because it is possible to have a N number of door Leafs.
**There are no errors in the code the SET @LeafCount is the only thing not working.
Thank you for the help!
--Set crate cost
DECLARE @LeafCount INT=0,@CrateCost MONEY=0;
SET @LeafCount = @SingleDoorCount + (@DoubleDoorCount * 2);
IF(@LeafCount < 6)
SELECT @CrateCost = 160.00;
IF(@LeafCount >5 and @LeafCount <11)
SET @CrateCost = 310.00;
SET @shippingcost = @DoubleDoorCost + @SingleDoorCost + @CrateCost;
SELECT @shippingcost 'ShippingCost'
Dam again!
July 6, 2011 at 3:23 pm
The only way I see @LeafCount not getting the right total is if @SingleDoorCount or @DoubleDoorCount is null.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 6, 2011 at 3:41 pm
I've DECLARED the Count variables to =0, this should set them correctly unless i am wrong. Everything evaluates correctly until i get to the part in question, which is the very last set of statements.
I am going to post the entire sproc so that it will have you a bigger insight.
------------------------------------------------------------------------------------
USE [StorefrontDoors]
GO
/****** Object: StoredProcedure [dbo].[uspShippingCost_Select] Script Date: 07/06/2011 16:31:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[uspShippingCost_Select]
@CartID VARCHAR(20),
@ShopperID UNIQUEIDENTIFIER
AS
DECLARE @DoubleDoorCount INT=0,@SingleDoorCount INT=0,
@DoubleDoorCost MONEY=0,@SingleDoorCost MONEY=0,@ShippingCost MONEY=0
SELECT @SingleDoorCount = dbo.fn_GetSingleDoorCount(@CartID,@ShopperID);
SELECT @DoubleDoorCount = dbo.fn_GetDoubleDoorCount(@CartID,@ShopperID);
--
IF(@SingleDoorCount = 1)
SELECT @SingleDoorCost = SingleDoor FROM dbo.utbShipping
--
IF(@DoubleDoorCount = 1)
SELECT @DoubleDoorCost = DoubleDoor FROM dbo.utbShipping
IF(@SingleDoorCount > 1)
BEGIN
-- weight * pound + base
SELECT @SingleDoorCost = utbShipping.SingleWeight *
@SingleDoorCount *
utbShipping.BasePlusPound
+
utbShipping.Base
FROM dbo.utbShipping
END
IF(@DoubleDoorCount > 1)
BEGIN
-- weight * pound + base
SELECT @DoubleDoorCost = utbShipping.DoubleWeight *
@DoubleDoorCount *
utbShipping.BasePlusPound
+
utbShipping.Base
FROM dbo.utbShipping
END
--Set crate cost
DECLARE @LeafCount INT=0,@CrateCost MONEY=155.00;
SET @LeafCount = @SingleDoorCount + @DoubleDoorCount * 2;
IF(@LeafCount < 6)
SET @CrateCost = 170.00;
IF(@LeafCount >5 and @LeafCount <11)
SET @CrateCost = 310.00;
SET @shippingcost = @DoubleDoorCost + @SingleDoorCost + @CrateCost;
SELECT @shippingcost 'ShippingCost'
Part Not Evaluating
-------------------------------
DECLARE @LeafCount INT=0,@CrateCost MONEY=155.00;
SET @LeafCount = @SingleDoorCount + @DoubleDoorCount * 2;
IF(@LeafCount < 6)
SET @CrateCost = 170.00;
IF(@LeafCount >5 and @LeafCount <11)
SET @CrateCost = 310.00;
Dam again!
July 6, 2011 at 4:10 pm
Very cool. Here is how we do it in the hood: fill in the @CartID and @ShopperID in the script below pulled from your proc and run it. It'll show us the values at each step.;-)
DECLARE @CartID VARCHAR(20)
DECLARE @ShopperID UNIQUEIDENTIFIER
SET @CartID =
SET @ShopperID =
DECLARE @DoubleDoorCount INT=0,@SingleDoorCount INT=0,
@DoubleDoorCost MONEY=0,@SingleDoorCost MONEY=0,@ShippingCost MONEY=0
SELECT @SingleDoorCount = dbo.fn_GetSingleDoorCount(@CartID,@ShopperID);
SELECT @DoubleDoorCount = dbo.fn_GetDoubleDoorCount(@CartID,@ShopperID);
PRINT @SingleDoorCount
PRINT @DoubleDoorCount
--
IF(@SingleDoorCount = 1)
SELECT @SingleDoorCost = SingleDoor FROM dbo.utbShipping
--
IF(@DoubleDoorCount = 1)
SELECT @DoubleDoorCost = DoubleDoor FROM dbo.utbShipping
IF(@SingleDoorCount > 1)
BEGIN
-- weight * pound + base
SELECT @SingleDoorCost = utbShipping.SingleWeight * @SingleDoorCount * utbShipping.BasePlusPound + utbShipping.Base
FROM dbo.utbShipping
END
PRINT @SingleDoorCost
IF(@DoubleDoorCount > 1)
BEGIN
-- weight * pound + base
SELECT @DoubleDoorCost = utbShipping.DoubleWeight * @DoubleDoorCount * utbShipping.BasePlusPound + utbShipping.Base
FROM dbo.utbShipping
END
PRINT @DoubleDoorCost
--Set crate cost
DECLARE @LeafCount INT=0,@CrateCost MONEY=155.00;
SET @LeafCount = @SingleDoorCount + @DoubleDoorCount * 2;
PRINT @LeafCount
IF(@LeafCount < 6)
SET @CrateCost = 170.00;
IF(@LeafCount >5 and @LeafCount <11)
SET @CrateCost = 310.00;
SET @shippingcost = @DoubleDoorCost + @SingleDoorCost + @CrateCost;
--SELECT @shippingcost 'ShippingCost'
PRINT @shippingcost
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 6, 2011 at 4:35 pm
Thanks!
Dam again!
July 7, 2011 at 7:05 am
Does that mean you found the problem? What was it?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply