Cannot compare decimal to varchar but real,float,int works fine

Question

I have a bug in the system which I tried to simplify in the code below:

declare @a decimal
select @a = 2.111

if  ''  !=  @a
select 'does not work'

when i define @ as a decimal it does not work. I get an error message "Error converting data type varchar to numeric". However if i define @a as float, money or real with the same value 2.111 it works

declare @a money
select @a = 2.111

if  ''  !=  @a
select 'works'

Could someone provide a solution if the column is decimal?

Answer 1

This is documented in Books On Line.

http://technet.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

SQL Server returns an error message when non-numeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

This does not really answer the question of "Why", but at least it is documented.

Answer 2

i think you need to test for IS NULL instead of empty as it is decimal value

if @a IS NULL

here is the sql to cast the value

declare @a double
select @a = 2.111

if  ''  !=  cast(@a as varchar(30))
select 'works'