Conversion Failed Varchar to Int - Why ??

Question

Conversion Failed Varchar to Int - Why ??

homebrew01

SSC Guru

Points: 55548
More actions
April 5, 2015 at 5:11 pm

#316133

Why does this cause an error ??
Seems that SQL should just care if @Col_Input is NULL or not. If so convert the 0 to ' 0 '.
Why does it expect @Col_Input to be INT in the CASE statement ?
declare @Col_Input varchar(5)
declare @Col_Update varchar(5)
Set @Col_Input = 'XYZ'
set @Col_Update = case when @Col_Input is null then 0 else @Col_Input end
select @Col_Update
Conversion failed when converting the varchar value 'XYZ' to data type int.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

homebrew01 (4/5/2015)
Why does this cause an error ??
Seems that SQL should just care if @Col_Input is NULL or not. If so convert the 0 to ' 0 '.
Why does it expect @Col_Input to be INT in the CASE statement ?
declare @Col_Input varchar(5)
declare @Col_Update varchar(5)
Set @Col_Input = 'XYZ'
set @Col_Update = case when @Col_Input is null then 0 else @Col_Input end
select @Col_Update
Conversion failed when converting the varchar value 'XYZ' to data type int.

Quick point, the case statement can only return one data type, try this for size

😎

declare @Col_Input varchar(5)

declare @Col_Update varchar(5)

Set @Col_Input = 'XYZ'

set @Col_Update = case when @Col_Input is null then '0' else @Col_Input end

select @Col_Update

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I came across this bug earlier today and did fix the code. But I was puzzled about why it failed.

I hadn't really though about this:

" the case statement can only return one data type"

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

homebrew01 (4/5/2015)
I came across this bug earlier today and did fix the code. But I was puzzled about why it failed.
I hadn't really though about this:
" the case statement can only return one data type"

It's not a bug. That's the way it works. Short circuiting only works down to where the short circuit ends including the datatype of the THEN. From Books Online...

ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

What that all translates to is this...

--===== This works just fine because of short ciruiting.

SELECT CASE WHEN 1=1 THEN 1 ELSE 'One not equal to One' END;

--===== This fails because the first "THEN" was not short circuited.

SELECT CASE WHEN 1=0 THEN 1 ELSE 'One not equal to Zero' END;

Notice that the message from the second CASE example is never selected. Instead, it is raised as an error. It's an old trick that can sometimes be used in place of RAISERROR in places like functions to let people know more about what's going on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)