ISNULL vs CASE Return Type
c#, sql, t-sql
Solution
To expand on @dasblinkenlight's explanation:
The real issue is that, per the documentation, `case` expressions
return the highest precedence type from the set of types in *result_expressions* and the optional *else_result_expression*. For more information, see Data Type Precedence (Transact-SQL).
While `isnull()`
returns the same type as *check_expression*
In your case, that is a `bit`. Your `case` expression:
CASE
WHEN a.column1 IS NULL THEN 0
ELSE a.column1
END
has execution paths that returns two different data types, an `int` (the literal value `0`) and a `bit` (the value of the column). If you look at the data type precedence chart linked above, `int` is of higher precedence than `bit` and thus the `bit` value is cast to an `int`.
That is because the conversion from `bit` to `int` is a widening conversion. Sql Server's `bit` type essentially, a 1-bit integer value and so no data is lost. The conversion from `int` to `bit` is a narrowing conversion and the possibility of data loss exists (at least in concept).
You wouldn't have encountered this problem, if the DBA had used `coalesce()` instead of `isnull()`, which would have been my personal choice, as `coalesce()` is defined to return a type similar to that of `case`. `coalesce()`
Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.
Problem
I recently ran into an interesting issue with changing `CASE` statements to `ISNULL` functions in TSQL. The query I was working with is used to get some user attributes and permissions for a website I work on. Previously the query had a number of `CASE` statements similar to the following: NOTE: `a.column1` in the example is of type `bit` in the table. Also note that the `[CanDoSomething]` result column is sometimes used as a string in the website. ``` SELECT ... CASE WHEN a.column1 IS NULL THEN 0 ELSE a.column1 END [CanDoSomething] ... FROM a ``` The DBA replaced these `CASE` statements with the `ISNULL` function: ``` SELECT ... ISNULL(a.column1, 0) [CanDoSomething] ... FROM a ``` This seems like a fine change, but it caused something unexpected when retrieving the data in C#. With the previous query, the value of the `[CanDoSomething]` column when accessed from a `DataTable` in C# was `1` or `0`. When we changed to using `ISNULL`, the value in C# was then changed to `true` or `false` which, when treated as a string, are obviously not the same as `1` or `0`. The errors this caused have already been addressed. I'm just curious why `ISNULL` returns a different value than an equivalent `CASE` statement and I can't seem to find any answers on Google.