Search
  • Kamil Kosno

T-SQL - pitfalls of CASE, COALESCE expressions and ISNULL function

SELECT CASE and COALESCE statements in SQL evaluate return data type based on precedence rules, detailed in Microsoft SQL docs online under the following link https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15.

ISNULL function uses different approach and takes the first value's data type as the output one.

Sometimes the results might be surprising when first encountered, unless we remember about those rules when typing our code.


SELECT CASE statement


Suppose we have a case statement intended to select a datetime value or the date value, depending on some condition:


DECLARE 
@Date DATE = '20210611',
@DateTime DATETIME2(7) = SYSDATETIME(),
@BitCheck BIT = 1;

SELECT CASE WHEN @BitCheck = 1 THEN @DateTime ELSE @Date END AS test;


Above statement returns a DATETIME2(7) value, as expected:










Now, lets see what happens when the date gets chosen instead. To demonstrate such flow, we flip the value of the @BitCheck variable:

DECLARE 
@Date DATE = '20210611',
@DateTime DATETIME2(7) = SYSDATETIME(),
@BitCheck BIT = 0;

SELECT CASE WHEN @BitCheck = 1 THEN @DateTime ELSE @Date END AS test;

and here is the output:










Why is the result not a simple date? Well, if we refer to Microsoft's online documentation, we will find that the CASE statement adheres to the data type precedence order when evaluating the output data type, which places DATETIME2 higher in the hierarchy than the DATE data type:


















Fortunately in our example it has not caused any trouble, because we have not lost the detail.


In some scenarios we might want to apply formatting to a value, e.g. cut the last four digits in the DATETIME2 value in the previous example. CONVERT function is a perfect choice for such application, so we might be tempted to write something along those lines:

DECLARE 
@Date DATE = '20210611',
@DateTime DATETIME2(7) = SYSDATETIME(),
@BitCheck BIT = 1;

SELECT CASE WHEN @BitCheck = 1 THEN CONVERT(VARCHAR(23), @DateTime, 121) ELSE @Date END AS test;

However, this time we end up truncating more characters than we expected:












It is a clear demonstration of the precedence rules which in this case promote the DATE type to be the final output data type of the whole statement. Character data types e.g. NVARCHAR and VARCHAR sit at the very bottom of the ladder:















Therefore, in order to ensure our code results in correctly formatted dates, we need to explicitly cast each value of the statement to a varchar data type like so:

DECLARE 
@Date DATE = '20210611',
@DateTime DATETIME2(7) = SYSDATETIME(),
@BitCheck BIT = 1;

SELECT CASE WHEN @BitCheck = 1 THEN CONVERT(VARCHAR(23), @DateTime, 121) ELSE CAST(@Date AS VARCHAR(23)) END AS test;

Our result is now as expected:








Note that if we wanted the DATE option in the above statement to also appear in the exact same format, we have to force the conversion to a DATETIME2(3) or DATETIME first.

Otherwise we will lose the entire time part in the process. To verify this, flip the @BitCheck switch to 0 before executing the CASE statement:


@BitCheck BIT = 0;
SELECT CASE WHEN @BitCheck = 1 THEN CONVERT(VARCHAR(23), @DateTime, 121) ELSE CAST(@Date AS VARCHAR(23)) END AS test;

and here is the outcome:










Instead, we have to write it similar to:


SELECT CASE WHEN @BitCheck = 1 THEN CONVERT(VARCHAR(23), @DateTime, 121) ELSE CONVERT(VARCHAR(23), CAST(@Date AS DATETIME), 121) END AS test;

resulting in the date format being consistent:









The same statement can also be coded like so:


@BitCheck BIT = 1;
SELECT CONVERT(VARCHAR(23), CASE WHEN @BitCheck = 1 THEN @DateTime ELSE @Date END, 121) AS test;
SET @BitCheck = 0;
SELECT CONVERT(VARCHAR(23), CASE WHEN @BitCheck = 1 THEN @DateTime ELSE @Date END, 121) AS test;

although we have to be conscious of the fact that within the CASE statement itself the precedence will first be applied between DATE and DATETIME2, and then the conversion to VARCHAR(23) will take place afterwards.

If we were dealing with different data types we should always remember or refer to the precedence rules to ensure the consistent output data type.


As the last note - the order of the values in the case statement does not matter. The precedence order is applied at the statement scope accounting for all participating data types. In other words, both of the statements below will result in the same output data type:


SELECT CASE WHEN @BitCheck = 1 THEN @Date ELSE CONVERT(VARCHAR(23), @DateTime, 121) END AS test;

SELECT CASE WHEN @BitCheck = 0 THEN CONVERT(VARCHAR(23), @DateTime, 121) ELSE @Date END AS test;



ISNULL function and COALESCE expression


COALESCE expression behaves similarly to a CASE statement i.e. SQL Server actually rewrites it under the hood as a CASE expression. For instance:


SELECT COALESCE(@Date, @Datetime) as test

will be interpreted the same as:


SELECT CASE 
WHEN @Date IS NOT NULL THEN @Date
ELSE @DateTime
END AS test


As a consequence, the determination of the output data type follows the same highest type precedence as the CASE statement. Therefore, running the following code:


DECLARE 
@Date DATE = NULL,--'20210611',
@DateTime DATETIME2(7) = SYSDATETIME(),
SELECT COALESCE(@Date, @Datetime) as test


will result in the output type of DATETIME2(7):











Whereas the below statement:


DECLARE 
@Date DATE = NULL,--'20210611',
@DateTime DATETIME2(7) = SYSDATETIME();
SELECT COALESCE(@Date, CONVERT(VARCHAR(23), @Datetime, 121)) as test

causes truncation, due to conversion from the character string to a DATE data type:











ISNULL function, on the other hand, determines the output type based on the first value's type, as the following example demonstrates. The first statement:


SELECT ISNULL(@Date, CONVERT(VARCHAR(23), @Datetime, 121))

results in:










but when we swap the order of the values:


SELECT ISNULL(CONVERT(VARCHAR(23), @Datetime, 121), @Date)

then VARCHAR data type is chosen, based on the first value inside the parentheses:











There are other nuances in terms of differences between ISNULL function and the COALESCE expression, so if you are interested I recommend checking the online resources e.g. Microsoft's docs under the following link: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15


Conclusion


SELECT CASE and COALESCE expressions as well as ISNULL function, are very useful tools in the T-SQL developer's arsenal.

However, they follow strict rules to determine the output data type, which may not always be obvious, especially because those rules are different between CASE/COALESCE and ISNULL function.

In my experience, I prefer to be explicit and convert (or cast) each value separately to the expected output data type, when dealing with columns with varying data types between them.



5 views0 comments