ISSUE with SELECT @var = @var + myfield ...

  • Try this in SQL 2005:

    CREATE TABLE #TAB (MY_FIELD varchar(10), a int)

    INSERT INTO #TAB VALUES ('test1', 1)

    INSERT INTO #TAB VALUES ('test2',2)

    INSERT INTO #TAB VALUES ('test3',3)

    DECLARE @val VARCHAR(2000)

    SELECT MY_FIELD FROM #TAB

    --1

    SET @val=''

    SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))

    FROM #TAB

    SELECT @val --correct

    --2

    SET @val=''

    SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))

    FROM #TAB

    ORDER BY 1 --WRONG

    SELECT @val

    --3

    SET @val=''

    SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))

    FROM #TAB

    ORDER BY my_field --correct

    SELECT @val

    --4

    SET @val=''

    SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))

    FROM #TAB

    ORDER BY a --correct

    SELECT @val

    DROP TABLE #TAB

    We have had several major threads here on stuff like running totals that relied on this 'trick' to work. I was pretty surprised though that this simple example seems to output only the final value when the order by 1 is in place.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • If you look at the execution plan you can see that in the query using ORDER BY 1 the SORT takes place AFTER the Compute Scalar operation, which I assume is the variable assignment while in the other queries the SORT occurs BEFORE the Compute Scalar. This is apparently why the results are different.

    I also recall reading somewhere, and I don't recall where, that using column ordinal in the ORDER BY is not recommended.

Viewing 2 posts - 1 through 1 (of 1 total)

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