#0377 – SQL Server – Msg 206; Operand Type Clash; Return type of a CASE expression follows datatype precedence


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

377-CASE_ReturnType

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

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

Advertisements

One thought on “#0377 – SQL Server – Msg 206; Operand Type Clash; Return type of a CASE expression follows datatype precedence

  1. Pingback: #0384 – SQL Server – Basics – Implicit Conversion – Blank strings (”) are treated as integers | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s