Assign multiple values using CASE in a Select Statement

Hello All,
I have a need to assign the values of the three variables in one select
statement. Currently, this is done using three
different Select statements :
Select @.Male = SMnemonic from sex where SName = 'Male'
Select @.Female = SMnemonic from sex where SName = 'Female'
Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
I would like to replace with just one Select statement. I thought that this
is simple, however, I'm getting vague results :
NULL
(1 row(s) affected)
NULL
(1 row(s) affected)
Unknown Mnemonic is : U
(1 row(s) affected)
Thanks,
Gopi
CREATE TABLE [dbo].[Sex] (
[SCode] [char] (10) ,
[SName] [varchar] (50),
[SMnemonic] [char] (10)
)
GO
Select * from Sex order by SCode
SCode SName SMnemonic
-- ---- --
1 Male
M
2 Female
F
3 Unknown
U
4 Not Known
NK
Select SMnemonic from sex where SName = 'Male'
Select SMnemonic from sex where SName = 'Female'
Select SMnemonic from sex where SName = 'Unknown'
Declare @.Male char(10)
Declare @.Female char(10)
Declare @.Unknown char(10)
Set @.Male = 'Junk'
Set @.Female = 'Junk'
Set @.Unknown = 'Junk'
Select @.Male = CASE SName
WHEN 'Male' THEN SMnemonic
END ,
@.Unknown = CASE SName
WHEN 'Unknown' THEN SMnemonic
END ,
@.Female = CASE SName
WHEN 'Female' THEN SMnemonic
END
from Sex
WHERE SName IN ('Male','Female','Unknown')
Select 'Female Mnemonic is : ' + @.Female
Select 'Male Mnemonic is : ' + @.Male
Select 'Unknown Mnemonic is : ' + @.UnknownTry,
Select
@.Male = case when SName = 'Male' then SMnemonic else @.Male end,
@.Female = case when SName = 'Female' then SMnemonic else @.Female end,
@.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
from
sex;
AMB
"rgn" wrote:

> Hello All,
> I have a need to assign the values of the three variables in one select
> statement. Currently, this is done using three
> different Select statements :
> Select @.Male = SMnemonic from sex where SName = 'Male'
> Select @.Female = SMnemonic from sex where SName = 'Female'
> Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
> I would like to replace with just one Select statement. I thought that thi
s
> is simple, however, I'm getting vague results :
> --
> NULL
> (1 row(s) affected)
> --
> NULL
> (1 row(s) affected)
> --
> Unknown Mnemonic is : U
> (1 row(s) affected)
>
> Thanks,
> Gopi
> CREATE TABLE [dbo].[Sex] (
> [SCode] [char] (10) ,
> [SName] [varchar] (50),
> [SMnemonic] [char] (10)
> )
> GO
> Select * from Sex order by SCode
> SCode SName SMnemonic
> -- ---- --
> 1 Male
> M
> 2 Female
> F
> 3 Unknown
> U
> 4 Not Known
> NK
>
> Select SMnemonic from sex where SName = 'Male'
> Select SMnemonic from sex where SName = 'Female'
> Select SMnemonic from sex where SName = 'Unknown'
>
> Declare @.Male char(10)
> Declare @.Female char(10)
> Declare @.Unknown char(10)
> Set @.Male = 'Junk'
> Set @.Female = 'Junk'
> Set @.Unknown = 'Junk'
>
> Select @.Male = CASE SName
> WHEN 'Male' THEN SMnemonic
> END ,
> @.Unknown = CASE SName
> WHEN 'Unknown' THEN SMnemonic
> END ,
> @.Female = CASE SName
> WHEN 'Female' THEN SMnemonic
> END
> from Sex
> WHERE SName IN ('Male','Female','Unknown')
> Select 'Female Mnemonic is : ' + @.Female
> Select 'Male Mnemonic is : ' + @.Male
> Select 'Unknown Mnemonic is : ' + @.Unknown
>
>|||Alejadro,
Thanks a Million. I see the problem. Since ELSE part is missing it is
assigning NULLs and the reason why the last
variable, in this case Unknown, retains the value.
Gopi
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BB446702-10C6-47D3-91F7-3C6E9FD1D4FC@.microsoft.com...
> Try,
> Select
> @.Male = case when SName = 'Male' then SMnemonic else @.Male end,
> @.Female = case when SName = 'Female' then SMnemonic else @.Female end,
> @.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
> from
> sex;
>
> AMB
> "rgn" wrote:
>