Using SELECT statement to assign variable

Question

Could you explain me the strange behaviour?

DECLARE @t VARCHAR(256) = ''

SELECT @t = @t + CAST(smb.symbol AS VARCHAR(256))
FROM (
    SELECT 1,'7'
    UNION ALL
    SELECT 2,'8'
    UNION all
    SELECT 3,'9'
) AS smb(n, symbol)
ORDER BY n

SELECT @t

Outputs:

789

Thats OK for me.

DECLARE @t VARCHAR(256) = ''

SELECT @t = @t + CAST(smb.symbol AS VARCHAR(256))
FROM (
    SELECT NUMS.N-1 AS N, CHAR(N-1) AS symbol
    FROM (
        SELECT 1 + n.n1 + nn.n2 * 10 + nnn.n3 * 100 as N
        FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n(n1)
            CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS nn(n2)
            CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS nnn(n3)
    ) AS NUMS
    WHERE NUMS.N BETWEEN 56 AND 58
) AS smb(N, symbol)
ORDER BY smb.N

SELECT @t

Outputs:

9

So why does the second example outputs the last symbol only?

You are relying on undocumented and unguaranteed behaviour. This approach to concatenation doesn't always work. Possible duplicate of nvarchar concatenation / index / nvarchar(max) inexplicable behavior — Martin Smith
– Martin Smith, Commented May 13, 2013 at 10:32
Want to see something funny? Remove ORDER BY from your second query and it'll return 789 as well :). +1 for executable question. — AdamL
– AdamL, Commented May 13, 2013 at 10:43
Thanks to everybody! The best practice for me is to avoid undocumented behaviours. — John Wales
– John Wales, Commented May 17, 2013 at 11:32

Serge · Accepted Answer · 2013-05-13 12:21:19Z

4

Don't rely on order by when using mutlirows variable assignment.

try this for instance:

DECLARE @c INT = 0

SELECT
 @c = @c + x
FROM (VALUES(1),(2),(3)) AS src(x)
WHERE x BETWEEN 1 AND 3 
ORDER BY 1 - x DESC

SELECT @c

SET @c = 0

SELECT
 @c = @c + x
FROM (VALUES(1),(2),(3)) AS src(x)
WHERE x BETWEEN 1 AND 3 
ORDER BY x DESC

SELECT @c

http://sqlmag.com/sql-server/multi-row-variable-assignment-and-order

edited May 13, 2013 at 12:21

answered May 13, 2013 at 12:12

Serge

6,7425 gold badges33 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

It still isn't guaranteed even in the absence of ORDER BY..

Even without ORDER BY, we do not guarantee that @var = @var + <expression> will produce the concatenated value for any statement that affects multiple rows.

The only reliable method is to use a completely different approach.

Collectives™ on Stack Overflow

Using SELECT statement to assign variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related