Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implicit Conversions


Implicit Conversions

Author
Message
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Hm, the answer is kind of paradox.

Either the "the varchar is converted to int" or an "error occurs". But not both.
I would have preferred an answer like this:
An error occurs during conversion from varchar to int.

But i should mostly blame myself for not reading the answer to the end...

Best Regards,

Chris Büttner
shamshudheen
shamshudheen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 211
john.arnott (5/6/2008)
Excellent question in that to understand the result, you have to step through the entire process.
--The statements compile.
--Each "select" is executed.
--Then the two result sets are combined to effect the Union. That's where the error occurs.

That's why even though the alpha character "B" was earlier in the code, the execution tried to convert it to the higher precedence integer type to satisfy the column type forced by the later value "4".

The compiler couldn't catch this as it won't look at the values, only the datatypes. If the character input had been a digit (in quotes), it would run. This works:

select '3'
union
select 4



good explanation,
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
Hmm, getting late to this, but I'm having difficulty accepting the answer provided here. Here is the exact result I received on a SQL Server 2000 instance:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'B' to a column of data type int.

Note the Syntax error - does this not fly in the face of all the explanations thus far??

(or is this a SQL Server 2005-specific question / answer and I simply didn't read properly?)

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
Tao,

I think the difference is just in the wording of the error message. My SQL 2000 instance gives:
Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'B' to a column of data type int.


and my 2005 gives:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'B' to data type int.


Note that both versions call it Msg 245, implying that it's the same error and the text has been corrected/clarified in the newer version. It's not really a syntax error at all, but a conversion error at the time the UNION is attempted.
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
ah-hah, thanks John, that makes a whole lot of sense!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
good question with great discussion :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search