Reputation: 448

Weird SQL Server behavior

What´s the name of the behavior in the statement below?

Create table dbo.Foo(name varchar(10))

insert dbo.Foo (name)
select 'Weird'
union
select 'SQL'
union
select 'Server'
union
select 'behavior'

declare @title varchar(max)
set @title = ''
select @title = name + ' ' + @title from dbo.Foo

select @title

--returns 'Weird SQL Server behavior'

This can also be done with integers. I want to know the name for this behavior, that mix scalar and set operations.

Upvotes: 0

Answers (5)

Alex Bagnolini

Reputation: 22382

Nobody mentioned that SELECT @title = name FROM dbo.Foo is the easier way to pick a value from a table and put it in a variable inside a SQL BATCH.

So, select @title = name + ' ' + @title from dbo.Foo picks the value of each row in dbo.Foo and add it in the end of @title.

Upvotes: 0

Remus Rusanu

Reputation: 294217

This is one of the methods used sometimes to aggregate strings in SQL, see Concatenating Row Values in Transact-SQL. Is no recommended because it relies on order of execution. On a real table ou can get 'Weird SQL Behavior' but also 'Behavior Weird Server SQL'. The problem is documented in PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location.

Upvotes: 1