Case Statement in a Stored Procedure

Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure?
I'm trying to do the following:
CASE
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
ELSE 'no room number'
END
Is there another way of doing this?
Any help would be appreciated.
Thanks>> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure? <<
Yes, you can do it. It is well documented in SQL Server Books Online with
examples too. Did you come across any problems using it?
--
- Anith
( Please reply to newsgroups only )|||HI Berny,
This looks fine, but it's hard to tell if there's an alternative without
knowing what the business rules are, what the tables look like, what the
data within those tables looks like, and what the expected output is
supposed to be. A case statement with multiple WHEN statements will work
fine in a stored procedure, but you might be able to accomplish the same
thing using a UNION and specific WHERE Clauses for each UNION. It might be
better, but then again, it might not be...without knowing all of the
details, who knows...
HTH
--
Regards,
Don R. Watters
Data Group Manager
PhotoWorks, Inc.
"Berny" <BlancoB at msn Dot com> wrote in message
news:OrJK7f4uDHA.2148@.TK2MSFTNGP12.phx.gbl...
> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
> statements in a stored Procedure?
> I'm trying to do the following:
> CASE
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
> RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> ELSE 'no room number'
> END
> Is there another way of doing this?
> Any help would be appreciated.
> Thanks
>