Tuesday, April 5, 2011

SQL Server - CASE within a CASE checking for NULL

I am still learning SQL so this may seem a very odd question, but is this the best way to use CASE within a CASE to check for NULL?

@FN_InputDt datetime)
RETURNS varchar(3)
as 
BEGIN

DECLARE  @Result    varchar(3),
         @MonthNo   int 

Set @MonthNo = datepart(m,@FN_InputDt)

Set @Result = 
        CASE WHEN @FN_InputDt IS NOT NULL then
        CASE @MonthNo
            WHEN  1 then 'JAN'                                                  
            WHEN  2 then 'FEB'
            WHEN  3 then 'MAR'
            WHEN  4 then 'APR'
            WHEN  5 then 'MAY'
            WHEN  6 then 'JUN'
            WHEN  7 then 'JUL'                   
            WHEN  8 then 'AUG'
            WHEN  9 then 'SEP'
            WHEN 10 then 'OCT'
            WHEN 11 then 'NOV'
            WHEN 12 then 'DEC'
        END
        END

        RETURN @Result
    END
From stackoverflow
  • If @FN_InputDt is null then @MonthNo will also be null, so you can just skip checking if @FN_InputDt is null.

    You can skip the other case also by just using @MonthNo as index to pick part of a string:

    set @Result = substring(
      'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',
      @MonthNo * 3 - 2,
      3
    )
    

    If @MonthNo is null @Result will also be null.

    JMS49 : Awesome! Thanks so much.
    Hogan : -1, See Jeff's comment, there is a library function to get the month name
    Guffa : @Hogan: Yes, that alternative is well worth mentioning, but it doesn't give the exact same result as the function in the question.
    Hogan : @Guffa : Good to know, how is it different?
  • Set @Result = Left(DateName(m,@FN_InputDt) , 3)

    This converts the month to the name and only displays the first 3 characters.

    Hogan : @jeff : isn't that DatePart not DateName?
    Jeff O : @Hogan - DatePart would return the numerical value of the month and not the actual name of the month. 12 instead of 'Dec' for example.

0 comments:

Post a Comment