Home Forums SQL Server 2012 SQL Server 2012 - T-SQL TSQL no longer working in SQL2012 RE: TSQL no longer working in SQL2012

  • May 27, 2014 at 7:58 am

    #1716693

    Ryan Keast (5/27/2014)

    Hi there,

    I had the following script that used to work in SQL2000 but now that I have migrated it, it no longer does.

    ,TEN.[tncy-sys-ref]

    + CASE WHEN PER.[GENDER] = 'M' THEN '1'

    WHEN PER.[GENDER] = 'F' THEN '2'

    ELSE '0'END AS 'ID'

    This used to either add a 1 or a 2 to the end of the TEN.[tncy-sys-ref]. What it does now is actually make an addition.

    Example the TEN.[tncy-sys-ref] is 25337.

    On SQL2000 it is 253371 for a Male or 253372 for a female.

    On SQL2012 it's coming back 25338 for a male and 25339 for a female.

    I need it to concatenate instead of addition.

    If that's true, then something went haywire with the migration because that code will still work as you have it if TEN.[tncy-sys-ref] is still a VARCHAR() and could only work as is if it was a VARCHAR(). Check the old system that you migrated and see if the column was, infact a VARCHAR() because I think it was and something changed it during the migration.

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