Help with CASE in Stored Procedure

Hi there,
I have a table with the following columns:
wgt_id
wgt_lower_weight
wgt_higher_weight
wgt_country
wgt_Parcel_Price
wgt_RMSD_Price
wgt_RMSD_Pre_9_Price
wgt_RMSD_Pre_1_Price
wgt_RMSD_Sat_Price
wgt_Citylink_Price
wgt_Citylink_Sat_Price
I want to create a stored procedure that is passed:
weight
country
shippingtype
This is what I have so far:
SELECT *
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower < @.Weight)
AND (wgt_weight_higher > @.Weight)
I need to add the parameter shippingtype. This will select the
matching column and output the price. I think it needs the use of CASE
but I can't figure it out. Can anyone help me create my store
procedure?
This is what I have tried but its not returning the Price, its
returning all the rows but blank.
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(10))
AS
SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
Price
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO
Any ideas where I am going wrong?
|||(a) never use sp_ prefix on stored procedures.
(b) provide DDL for the tblShippingRates table (another questionable prefix,
btw), some sample data, and desired results. I have no idea what data is in
the table, what parameter values you are passing in, what should be returned
by the query, and what "all the rows but blank" means...
Aaron Bertrand
SQL Server MVP
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186057919.366437.11420@.d55g2000hsg.googlegro ups.com...
> This is what I have tried but its not returning the Price, its
> returning all the rows but blank.
> CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
> @.Weight decimal(12,2), @.Shipping varchar(10))
> AS
> SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
> WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
> WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
> WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
> WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
> WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
> Price
> FROM dbo.tblShippingRates
> WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
> AND (wgt_weight_higher >= @.Weight)
> GO
> Any ideas where I am going wrong?
>
|||Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Pricewgt_Citylink_Sat_Price
00.5uk4.9013.005.608.1010.4025.40
0.511uk6.2015.007.009.5010.4025.40
1.012uk6.7018.709.2011.7010.4025.40
2.014uk9.7023.0010.4025.40
4.016uk23.0010.4025.40
6.018uk23.0010.4025.40
8.0110uk23.0010.4025.40
10.0115uk12.9027.90
15.0120uk15.4030.40
I am passing the stored procedure country = uk weight = 2 and shipping
= RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve
|||Oh, OK. So you'd have a CASE:
CASE @.shipping
WHEN 'RMSD9' then wgt_RMSD_Pre9_Price
WHEN 'RMSD1' then wgt_RMSD_Pre1_Price
...
END
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186062055.667781.242240@.r34g2000hsd.googlegr oups.com...
On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.
|||>I worked it out!
Your procedure suddenly started returning data because you changed AS
ShippingPrice to ShippingPrice = and CASE WHEN @.Shipping = to CASE @.Shipping
WHEN ? That doesn't seem right. Anyway, how about readability? Do you
need to repeat dbo.tblShippingRates 18 times?
CREATE PROCEDURE dbo.usp_GetShippingCharge
@.Country VARCHAR(2),
@.Weight DECIMAL(12,2),
@.Shipping VARCHAR(12)
AS
BEGIN
SET NOCOUNT ON;
SELECT
ShippingPrice = CASE @.Shipping
WHEN 'Parcel' THEN wgt_Parcel_Price
WHEN 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN wgt_Citylink_Price
WHEN 'CitylinkSat' THEN wgt_Citylink_Sat_Price
END
FROM
dbo.tblShippingRates
WHERE
wgt_country = @.Country
AND (@.Weight BETWEEN wgt_weight_lower AND wgt_weight_higher);
END
GO
Finally, since this will only ever return one column and *should* only be
returning one row, why not make it a scalar function, or at least capture
the data via an output parameter?
Aaron Bertrand
SQL Server MVP
|||On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:

>I worked it out!
Hi Dooza,
Good for you.
However, I think you'd be better off with a different design of your
table. As it is, you'll have to keep adding and removing columns and
changing your code every time a new courier option comes around, when an
option is removed, or even when an option is renamed.
Instead of different price columns for each courier option, you should
have one column Price and one column CourierOption. The latter should of
course be included in the table's key. That would make this query lots
easier!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Aug 2, 9:06 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:
> Hi Dooza,
> Good for you.
> However, I think you'd be better off with a different design of your
> table. As it is, you'll have to keep adding and removing columns and
> changing your code every time a new courier option comes around, when an
> option is removed, or even when an option is renamed.
> Instead of different price columns for each courier option, you should
> have one column Price and one column CourierOption. The latter should of
> course be included in the table's key. That would make this query lots
> easier!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Hi Hugo,
You are correct, and this is what I have ended up doing, as my
previous attempt didn't allow me to also save the type of shipping in
the database. The way I am doing it now is much better. I can now
create a drop down list with the available options for that particular
weight, before I couldn't do that, and I now have an ID for the
shipping type that I can store in the database with the order.
The user is now presented with the drop down list to select the type
of shipping, once selected the id of the shipping type is inserted
into the database, I will then lookup the ID and pass the price to the
cart to be included with the total. A much neater solution.
Thank you all for steering me in the right direction!
Dooza