Cast statement behaviour in Case statement

Hi All,

My intention is to add an four spaces string with the string ‘Hi’ if condition is true else need to return ‘8’

I had written an piece of code as shown below

Select

CASE  

         WHEN 1=1 THEN cast ('' as char (4))

         ELSE '8' 

      END

+'HI'

The result set of this piece of code is

 ‘ HI’     [one space followed by string ‘HI’]

I am expecting four spaces followed by a string ‘HI’, why it happened like this?

From the BOL :

The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

Here the data type of expression in else is varchar with length one.

Hence the expression in the THEN clause implicitly converted to varchar data type with length one so I got result set as "One space followed by an string ‘HI’"

I modified above piece code very slightly as mentioned below.

Select

CASE  

         WHEN 1=1 THEN cast ('' as char (4))

         ELSE cast (‘8’   as char (4))

      END

+'HI'

Now on execution of the above piece of code has given my expected output.

Here I am casting the data type and length of an expression in else clause.

The conclusion is all the result expressions should have a same data type or implicitly convertible if it is the case it will convertible to higher precedence data type with length.

Thanks...