Post reply

TSQL

  • April 7, 2009 at 1:57 pm

    #973781

    I tried in SQL 2005. It worked fine.

    While inserting, it perform implicit conversion of numerals into varchar.

    But if SELECTed, it does not convert it.

  • April 8, 2009 at 2:20 am

    #974064

    Julie Breutzmann (3/26/2009)

    While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.

    I totally agree with Julie on this.

    I'd rather learn a valuable lesson without getting points than learning nothing while getting thousands of points. :w00t:

    Jochen

  • April 8, 2009 at 2:32 am

    #974072

    Just in case anyone "in the know" is still on this thread, can anyone explain this phenomenon in SQL Server 2000?

    I looked for an explanation among the many (fascinating) comments on this thread, but didn't see this specifically addressed anywhere - I might well have missed it though.

    The "simple" case fails in the way that was originally expected by the author, and in line with documented conversion rules:

    Select 1

    union Select 2

    union Select 3

    union Select 4

    union Select 5

    union Select 6

    union Select 7

    UNION Select 'A'

    union Select 'B'

    union Select 'C'

    union Select 'D'

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the varchar value 'A' to a column of data type int.

    That is normal / expected SQL Server behaviour.

    When you add an INSERT (to a table with appropriate type) in SQL 2000, the error goes away:

    Create Table Test(col varchar(10))

    GO

    Insert into Test

    Select 1

    union Select 2

    union Select 3

    union Select 4

    union Select 5

    union Select 6

    union Select 7

    UNION Select 'A'

    union Select 'B'

    union Select 'C'

    union Select 'D'

    (11 row(s) affected)

    Does anyone know why/how this happens? Is it a bug, or expected behaviour?

    Sorry if this was already addressed, I would appreciate any comments/reminders pointing in the right direction.

    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.

  • April 8, 2009 at 6:12 am

    #974195

    I believe that this happens in SQL 2000 because the compile "reads-ahead" or anticipates the datatype expected by the table column and then back applies it to the source expressions. Implementing something like this is highly dependent on the internals of the compiler which was completely rewritten in 2005.

    So my guess is that that obscure feature was dropped as part of the rewrite. (These features can have some problematic side-effects too, though I cannot remember them at the moment).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 8, 2009 at 6:14 am

    #974199

    Jochen Vleming (4/8/2009)

    Julie Breutzmann (3/26/2009)

    While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.

    I totally agree with Julie on this.

    I'd rather learn a valuable lesson without getting points than learning nothing while getting thousands of points. :w00t:

    Jochen

    Actually, you get far more points by participating in the discussion. That's one of the nice things about the point system on this site.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 8, 2009 at 7:25 am

    #974259

    As I said before, the script in questions works fine in 2000 and 2005 as well.

    Without the INSERT, if you only SELECT it doesn't. The SELECT with different datatypes, does not do an implicit conversion.

  • April 8, 2009 at 8:02 am

    #974309

    RBarryYoung (4/8/2009)

    Jochen Vleming (4/8/2009)

    Julie Breutzmann (3/26/2009)

    While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.

    I totally agree with Julie on this.

    I'd rather learn a valuable lesson without getting points than learning nothing while getting thousands of points. :w00t:

    Jochen

    Actually, you get far more points by participating in the discussion. That's one of the nice things about the point system on this site.

    That's very true. Just look at the Top QotD points versus the Top Forum Posters points: http://www.sqlservercentral.com/TotalScores

  • April 16, 2009 at 11:52 am

    #979094

    arconen (3/26/2009)

    In future must show the version of MS SQL Server in order to prevent a misunderstanding.

    I answered right for version 2000, but for 2008 I made a mistake.

    I absolutely agree with that. And, it brings up another huge problem... these type of changes to the database engine are complete and utter non-sense that break huge amounts of code. Such "improvements" to the engine cause more pain than the Y2K problem ever thought of causing.

    --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)

  • June 23, 2009 at 7:18 am

    #1013895

    My take on ths is that SQL 2008 has introduced a serious defect! Changing to behave in this irrational manner has probably broken rather a lot of SQL.

    And since the behaviour in SQL 2008 is different from the behaviour in all previous versions, it would have been useful (to say the least) if the question had specified teh version.

    Tom

  • December 18, 2009 at 2:03 am

    #1093912

    With SQL Server 2005, I am able to create the table with no error, so the answer is wrong.

    However, when subsequently I query the table, the conversion error appears.

    1 "SELECT col FROM test" succeeds => 1234567ABCD

    2 "SELECT col FROM test WHERE IsNumeric(col)=1" succeeds => 1234567

    3 The query given fails with the conversion error

    Regards, Jon Summers

  • January 8, 2010 at 4:42 am

    #1100783

    Hi there

    I work in a SQL 2000 developer edition. when I run the provided script, it runs successfully returning rows 1 through 6.

    I did not dound any conversion error.

    Ashok

  • January 21, 2011 at 2:22 am

    #1276422

    This works

    Insert into test Select 'A' union Select 1;

    also

    Insert into test Select 1 union Select 'A'; works

    but only select statement not works.

    Select 1 union Select 'A';

    Give error :

    Syntax error converting the varchar value 'A' to a column of data type int.

  • February 15, 2013 at 11:01 pm

    #1588581

    good explanation John....

    the error is coming due to incompatibility of datatypes in UNION....

    but if we do like this -

    DECLARE @id varchar(10)

    set @id = 'A'

    select isnumeric(@id)

    It always gives 1

    why?

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

Viewing 13 posts - 76 through 87 (of 87 total)

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