Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Implicit Conversions Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2008 11:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #495843
Posted Wednesday, May 7, 2008 12:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
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,
Post #496096
Posted Friday, August 15, 2008 4:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #553844
Posted Friday, August 15, 2008 5:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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.
Post #553856
Posted Friday, August 15, 2008 5:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #553857
Posted Monday, December 3, 2012 11:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,921, Visits: 2,345
good question with great discussion


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1392254
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse