InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:38:04 GMT20http://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxgood question with great discussion :-)Mon, 03 Dec 2012 23:24:52 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxah-hah, thanks John, that makes a whole lot of sense!Fri, 15 Aug 2008 17:57:29 GMTTao Klerkshttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxTao,I think the difference is just in the wording of the error message. My SQL 2000 instance gives: [code]Msg 245, Level 16, State 1, Line 1Syntax error converting the varchar value 'B' to a column of data type int.[/code]and my 2005 gives:[code]Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'B' to data type int.[/code]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. Fri, 15 Aug 2008 17:54:40 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxHmm, 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 1Syntax error converting the varchar value 'B' to a column of data type int.Note the [b]Syntax error[/b] - 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?)Fri, 15 Aug 2008 16:43:05 GMTTao Klerkshttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspx[quote][b]john.arnott (5/6/2008)[/b][hr]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 [b]execution[/b] 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:[code]select '3'union select 4[/code][/quote]good explanation,Wed, 07 May 2008 00:47:16 GMTshamshudheenhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxHm, 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...Tue, 06 May 2008 11:56:50 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxExcellent 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 [b]execution[/b] 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:[code]select '3'union select 4[/code]Tue, 06 May 2008 09:13:20 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxThat will teach me for speed reading the answers - missed the (error results) at the end of answer two! So picked answer 3! My bad...(But at least I knew it wouldn't work!)RodneyTue, 06 May 2008 03:48:52 GMTrodjkiddhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxI feel the same way, in addition note the wording:This is an example of an implicit conversion. In this case, an error is returned as the varchar is converted to an int, or an attempt is made, which returns an error. The int is of higher precedence than a varchar, so that is the order of conversions.What is it converted and when error says failed to convert and then you say varchar IS converted to an int and then you say OR attempt is made? Which one is it?Mon, 05 May 2008 16:13:47 GMTSponge-592981http://www.sqlservercentral.com/Forums/Topic494662-83-1.aspx[quote][b]Trace Herrell (5/5/2008)[/b][hr]This will not compile in SQL 2005....Conversion failed when converting the varchar value 'B' to data type int.[/quote]On the contrary, it did compile but it encountered an error when it ran. If you mistyped and put in "Skelect 'B' ..." it would not compile. Conversion failure is a runtime error, in this case because a varchar 'B' can't be converted to int. The compiler doesn't check to make sure you won't run into data type errors.Mon, 05 May 2008 13:27:09 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxThis will not compile in SQL 2005....Conversion failed when converting the varchar value 'B' to data type int.Mon, 05 May 2008 12:40:19 GMTTrace Herrell-425305http://www.sqlservercentral.com/Forums/Topic494662-83-1.aspx[quote][b]Steve Jones - Editor (5/5/2008)[/b][hr]That is not a compilation error. It's a run-time error when a conversion occurs. There are very few compilation errors, mostly syntax issues.[/quote]In my humble opinion the answer [quote]The varchar is converted in int (error results)[/quote] should have beenThe varchar is not converted into int (run time error results)Since this union statement will work (no run time error)select '10' Union Select 4and this will compileCREATE PROCEDURE Dbo.OopsASselect 'B' Union Select 4but will result in the run time error when invoked.Oh well looking back now .... I have gained knowledge from the QOD and that is its objective.Thanks Steve for making think even moreMon, 05 May 2008 09:13:11 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxAS previously stated: [quote][b]dunnjoe (5/5/2008)[/b][hr]That's why I feel answer b is not correct. Answer b says the value is converted to an int. The error message says the conversion failed. So, it appears to be a compilation error.[/quote]Unfortunately I came to the same conclusion ... the statement did NOT execute ... other than the error message[quote]Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'B' to data type int.[/quote] There were NO values returned!!Still a good QOD just a bad wording of possible answers ... to the detriment of many of usMon, 05 May 2008 08:56:20 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxThat is not a compilation error. It's a run-time error when a conversion occurs. There are very few compilation errors, mostly syntax issues.Mon, 05 May 2008 08:29:30 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspx[quote][b]bvesperman (5/5/2008)[/b][hr]I agree that this doesn't work. I was pretty sure it wasn't going to do an implicit conversion. Then I cut and pasted it into a query and executed it. And I was proven right. It failed. No results were returned. An error was returned. It things like this that makes me not try to read or follow information from sites like this one. Is there a setting in MSSQL that allows implicit conversion? I hope not. My databases need to maintain data integrity, and if implicit conversions are happening I can't trust my databases.[/quote]Yes, implicit conversions happen.It's not a problem, if you know how they work. Books Online has exact data on what they do and why and how.Mon, 05 May 2008 08:25:36 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxI agree that this doesn't work. I was pretty sure it wasn't going to do an implicit conversion. Then I cut and pasted it into a query and executed it. And I was proven right. It failed. No results were returned. An error was returned. It things like this that makes me not try to read or follow information from sites like this one. Is there a setting in MSSQL that allows implicit conversion? I hope not. My databases need to maintain data integrity, and if implicit conversions are happening I can't trust my databases.Mon, 05 May 2008 08:17:59 GMTbvespermanhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxThat's why I feel answer b is not correct. Answer b says the value is converted to an int. The error message says the conversion failed. So, it appears to be a compilation error.Mon, 05 May 2008 08:03:04 GMTdunnjoehttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxThis does not work on SQL Server 2005. You get the following error message:"Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'B' to data type int."Mon, 05 May 2008 07:21:02 GMTWilliam Vachhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxI have 2000, and it gave the "correct" result for me.Mon, 05 May 2008 06:56:10 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxPerhaps I missed something...But I believe this works on SQL2000, but 2005.There was a change to the behavior of unions....ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/47edefbd-a09b-4087-937a-453cd5c6e061.htmMon, 05 May 2008 06:24:00 GMTJason Miller-476791http://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxSimple but strong question. We require more this type of questions where you have to 'think' before answering. Before seeing Jeff's comment 'Average Question' :P, I thought it was 'good' question as the marks is '3' not the normal '1'. So I thought more than require. :hehe:Mon, 05 May 2008 02:04:13 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxI agree... judging from some of the "Why am I getting this error" questions on the forum, implicit conversions to "bite" people a lot. This "simple" question is a very good example of a lot of problems folks have with UNIONs and other places where columnar data is mixed to form a single column.Sun, 04 May 2008 10:07:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxI put it as 3 because I think implicit conversions cause a lot of issues, waste time, and I see a lot of questions on these. So it's important.I may go back and "re-rate" some questions, moving some 1s to 2s and 2s to 3s or 4s, trying to weight them a little more when you have to think. I'll adjust points for correct answers as well if I do.Sun, 04 May 2008 09:50:59 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspx3 Marks isn't bad or good... it means "average" and it is an "Average" question. :PSun, 04 May 2008 09:30:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxWhy 3 marks for this question??Sun, 04 May 2008 01:20:16 GMTgbpthomashttp://www.sqlservercentral.com/Forums/Topic494662-83-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/T-SQL/62806/">Implicit Conversions</A>[/B]Sat, 03 May 2008 12:01:08 GMTSite Owners