When we were working on a particular SSIS package that was extracting data from a legacy SQL Server database. We kept running into meta-data and datatype mismatch issues on a couple of databases. It was later that we realized that we were overlooking a basic aspect of the CASE expression – the return type.

When the CASE expression is evaluated, it is possible that different result expressions (what’s written in the THEN argument) are of different data-types. In such cases, the CASE expression would follow datatype precedence and return the highest datatype.

The example below is a simple CASE expression that evaluates 3 different inputs – an integer, a decimal and a character value. To demonstrate the return type, I will simply use the SELECT…INTO paradigm to create a new table on the fly.

USE tempdb;
GO
--Safety Check
IF OBJECT_ID('[dbo].[sqlTwinsTable]','U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[sqlTwinsTable];
END
GO

--Use 3 variables of different datatypes and 
--use a CASE expression to return from them
DECLARE @integerValue INT = 8;
DECLARE @decimalValue DECIMAL(19,4) = 20.16;
DECLARE @characterValue VARCHAR(200) = 'Character Value';

DECLARE @caseSwitch INT = 1;

SELECT CASE @caseSwitch WHEN 1 THEN @integerValue
                        WHEN 2 THEN @decimalValue
                        WHEN 3 THEN @characterValue
                        ELSE 'Undefined Value'
       END AS CaseOutput
INTO [dbo].[sqlTwinsTable]
GO

Checking the table definition of the newly created table, we see that it was created with the datatype “decimal” – even though the switch/input expression was such that it returns an integer.

USE tempdb;
GO
--Check the table definition
SELECT isc.TABLE_SCHEMA,
       isc.TABLE_NAME,
       isc.COLUMN_NAME,
       isc.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS isc
WHERE isc.TABLE_SCHEMA = 'dbo'
  AND isc.TABLE_NAME = 'sqlTwinsTable'
GO
--Check the returned value
SELECT CaseOutput
FROM [dbo].[sqlTwinsTable];
GO

As can be seen from the screenshot above, the return type of the CASE expression is the highest precedence datatype from the input result expressions across all the switch branches. Not considering this behaviour may cause issues if the calling application is datatype sensitive.

Word of caution

A classic example of this behaviour causing unexpected issues is when one has incompatible datatypes (which cannot be implicitly converted from one type to another) mixed in the same expression, as in this case.

--Safety Check
IF OBJECT_ID('[dbo].[sqlTwinsTable]','U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[sqlTwinsTable];
END
GO

--Use 3 variables of different datatypes and 
--use a CASE expression to return from them
DECLARE @integerValue INT = 8;
DECLARE @decimalValue DECIMAL(19,4) = 20.16;
DECLARE @characterValue VARCHAR(200) = 'Character Value';
DECLARE @dateValue DATE = '2016-07-18';

DECLARE @caseSwitch INT = 1;

--The DATE datatype has a higher precedence
--However, all inputs cannot be implicitly converted to DATE
--We will therefore get a data-type conversion error
SELECT CASE @caseSwitch WHEN 1 THEN @integerValue
                        WHEN 2 THEN @decimalValue
                        WHEN 3 THEN @characterValue
                        WHEN 4 THEN @dateValue
                        ELSE 'Undefined Value'
       END AS CaseOutput
INTO [dbo].[sqlTwinsTable]
GO

Msg 206, Level 16, State 2, Line 62
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 62
Operand type clash: decimal is incompatible with date

Further Reading

• CASE expression [MSDN Link]
• Data Type Precedence in SQL Server [MSDN Link]

Until we meet next time,
Be courteous. Drive responsibly.