View Full Version : Shipping Price


24sharon
04-03-2008, 02:14 AM
I want to make stored Procedure that get sum total of the order and return the ship price

my table schema attach :

in my procedure I want to get the order subTotal and return the ship price

For example if the subTotal is 60$ than return 300$

etc,

my problem is that if the "toPrice" = 0, than its mean "higher than"

How can I Write this Code, here is my try:

declare @subTotal int
set @subTotal = 60


SELECT [ShippingPolicyId]
,[Header]
,[fromPrice]
,[toPrice],[price]
FROM [dbo].[xxx_ShippingPolicy]
where @subTotal >=[fromPrice] and @subTotal <= [toPrice]
--where @subTotal >=[fromPrice] and case when [toPrice] > 0 then @subTotal <= [toPrice] else 1=1

pbaldy
04-03-2008, 08:46 AM
Rather than zero as a toPrice, I would use 9999999 (or some number in excess of anything you sell). That way you can use a non-equi join or lookup to get the shipping.