[RESOLVED] Case Statement

[shakti5385]

Hi all
I am facing some problem in the following please tell me that what is wrong.

DECLARE @TableName nvarchar(Max),@Abbreviation nvarchar(100)
SET @Abbreviation='HH'
select CASE @Abbreviation
WHEN 'HH' THEN SET @TableName='Hotel_History'
WHEN 'HA' THEN SET @TableName='Hotel_Accomodation'
WHEN 'HD' THEN SET @TableName='Hotel_Dining'
WHEN 'HB' THEN SET @TableName='Hotel_Banquet_Conference'
WHEN 'HF' THEN SET @TableName='Hotel_Facilities_Services'
WHEN 'HV' THEN SET @TableName='Hotel_Virtual_Tour'
WHEN 'HG' THEN SET @TableName='Hotel_Gallery'
ELSE SET @TableName='NotMatch'
END
PRINT @TableName

Thanks

[szlamany]

Case if not a logic-flow type of statement.

Although it certainly appears to be one - it's not at all like the VB SELECT/CASE.

It's really an in-line scalar function - so it's used like this instead.

DECLARE @TableName nvarchar(Max),@Abbreviation nvarchar(100)
SET @Abbreviation='HH'
Set @TableName=CASE @Abbreviation
                    WHEN 'HH' THEN 'Hotel_History'
                    WHEN 'HA' THEN 'Hotel_Accomodation'
                    WHEN 'HD' THEN 'Hotel_Dining'
                    WHEN 'HB' THEN 'Hotel_Banquet_Conference'
                    WHEN 'HF' THEN 'Hotel_Facilities_Services'
                    WHEN 'HV' THEN 'Hotel_Virtual_Tour'
                    WHEN 'HG' THEN 'Hotel_Gallery'
                    ELSE 'NotMatch'
                    END
PRINT @TableName

It's more like the IIF() function in VB6.

[edit]And it's always great to have an ELSE. CASE without an ELSE is non-deterministic - meaning that it can return a NULL. Non-deterministic expressions can causes issues in strange places.

We never write a CASE/WHEN that doesn't have an ELSE [/edit]

[shakti5385]

I make some modification your code and now it is working.

SELECT  @TableName= CASE @Table_Abbreviation
        WHEN 'HH' THEN 'Hotel_History'
        WHEN 'HA' THEN 'Hotel_Accomodation'
        WHEN 'HD' THEN 'Hotel_Dining'
        WHEN 'HB' THEN 'Hotel_Banquet_Conference'
        WHEN 'HF' THEN 'Hotel_Facilities_Services'
        WHEN 'HV' THEN 'Hotel_Virtual_Tour'
        WHEN 'HG' THEN 'Hotel_Gallery'
        ELSE 'NotMatch'
     END

Thanks

[szlamany]

oops - I had a stray SELECT - I have edited the post.

You could still use SET - since you simply filling a variable with a value.

[shakti5385]

Thanks for gerat help.