Systems Engineering and RDBMS

Data Type Precedence in CASE statements

Posted by decipherinfosys on December 8, 2008

This is one of the common issues faced by junior as well as senior DB Developers/DBAs. When you are using a CASE statement, you have to ensure that all the return values are of compatible data types else data type conversion failures can occur. Here is the data type precedence order that is followed by the SQL Server engine:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Let’s take this up with an example:

declare @i varchar(10)
set @i = ''
select case
when @i = '' then 'Empty String'
when @i = 'x' then 1
else 3 end

Execute the above snippet of the code and you will get this error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ‘Empty String’ to data type int.

Since the data-type for the output in the THEN condition is mixed (string as well as a number), the engine attempts to convert the string to number (based on the data type precedence link given above) and fails. Likewise, if we had a datetime data type:

declare @i varchar(10)
set @i = ''
select case
when @i = '' then 'Empty String'
when @i = 'y' then getdate()
else 3 end

You will get:

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting datetime from character string.

The safest solution is to have all the output to be the same data type so that the conversion issues because of the data type precedence do not take place:

declare @i varchar(10)
set @i = ''
select case
when @i = '' then 'Empty String'
when @i = 'y' then convert(varchar(20), getdate(), 120)
else '3' end

2 Responses to “Data Type Precedence in CASE statements”

  1. […] by decipherinfosys on December 22, 2008 In one of our blog posts, we had covered how the implicit conversion creates an issue in the CASE statement. In that case, one actually catches it before it becomes an issue since it will give an error when […]

  2. Craig Corbin said

    Thanks so much for this article. I had been fighting with this error, and your article pointed me to the error in my ways.

Leave a Reply

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

 
%d bloggers like this: