Systems Engineering and RDBMS

Archive for December 8th, 2008

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

Posted in SQL Server | 2 Comments »