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.googlegroups.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_Price wgt_Citylink_Sat_Pric
e
0 0.5 uk =A34.90 =A313.00 =A35.60 =A38.10 =A310.40 =A325.40
0=2E51 1 uk =A36.20 =A315.00 =A37.00 =A39.50 =A310.40 =A325.40
1=2E01 2 uk =A36.70 =A318.70 =A39.20 =A311.70 =A310.40 =A325.40
2=2E01 4 uk =A39.70 =A323.00 =A310.40 =A325.40
4=2E01 6 uk =A323.00 =A310.40 =A325.40
6=2E01 8 uk =A323.00 =A310.40 =A325.40
8=2E01 10 uk =A323.00 =A310.40 =A325.40
10.01 15 uk =A312.90 =A327.90
15.01 20 uk =A315.40 =A330.40
I am passing the stored procedure country =3D uk weight =3D 2 and shipping
=3D 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|||I worked it out!
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(12))
AS
SELECT ShippingPrice = CASE @.Shipping WHEN 'Parcel' THEN
dbo.tblShippingRates.wgt_Parcel_Price
WHEN 'RMSD9' THEN dbo.tblShippingRates.wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN dbo.tblShippingRates.wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN dbo.tblShippingRates.wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN dbo.tblShippingRates.wgt_Citylink_Price
WHEN 'CitylinkSat' THEN dbo.tblShippingRates.wgt_Citylink_Sat_Price
END
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO|||It seems that the selection criteria is based on weight and country. I
don't understand the RMSD9 bit. In your case, the query would be something
like:
select
wgt_Parcel_Price
from
MyTable
where
wgt_country = @.country
and
@.weight between wgt_weight_lower and wgt_weight_higher
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:1186060353.152602.170690@.q75g2000hsh.googlegroups.com...
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_Price wgt_Citylink_Sat_Price
0 0.5 uk 4.90 13.00 5.60 8.10 10.40 25.40
0.51 1 uk 6.20 15.00 7.00 9.50 10.40 25.40
1.01 2 uk 6.70 18.70 9.20 11.70 10.40 25.40
2.01 4 uk 9.70 23.00 10.40 25.40
4.01 6 uk 23.00 10.40 25.40
6.01 8 uk 23.00 10.40 25.40
8.01 10 uk 23.00 10.40 25.40
10.01 15 uk 12.90 27.90
15.01 20 uk 15.40 30.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|||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.
Aaron Bertrand
SQL Server MVP
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eH8U5iQ1HHA.464@.TK2MSFTNGP02.phx.gbl...
> It seems that the selection criteria is based on weight and country. I
> don't understand the RMSD9 bit. In your case, the query would be
> something
> like:
> select
> wgt_Parcel_Price
> from
> MyTable
> where
> wgt_country = @.country
> and
> @.weight between wgt_weight_lower and wgt_weight_higher
> --
> Tom|||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.|||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.googlegroups.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