Why subquery inside recursive string concatenation

Question

Suppose I have two tables:

• TABLE1: Has a list of IDs I need to process.
• TABLE2: Has key-value pairs of ID's and values.

I need to retrieve the values from TABLE2 for all the IDs in TABLE1, as a single string. In order to achieve this, I made the next query:

DECLARE @id INT, @value VARCHAR(10);

SELECT @id=0, @value='';

SELECT
    @value = @value + (SELECT TOP 1 value FROM TABLE2 WHERE id=@id) + '-',
    @id = @id+1
FROM TABLE1

But when I run this query, the subquery always returns null (and, therefore, @value=NULL at the end). My question is: Why does the subquery SELECT TOP 1 value FROM TABLE2 WHERE id=@id always returns NULL, even if the ID is found in TABLE2?

Answer 1

Yet another solution (this one only appends the '-' delimiter between values from table2 if the value from table2 is NOT NULL):

declare @id int, @value varchar(max);
select @id = 0, @value='';

select 
    @value = @value + isnull(value,'') + case when value is null then '' else '-' end
from 
    table2 t2
where 
    t2.id in (select id from table1)

Answer 2

This is a very weird way of trying to implement a treewalker, increasing @id in the SELECT and expecting it to apply to the subquery. It doesn't work because SQL Server locks the value of @id for the subquery expression as a constant right at the query setup phase.

See this example, where the returned @value clearly indicates that @id is locked at 1. With your question, it was locked at 0, hence each subquery will return NULL, ostensibly because there is no match for @id = 0.

create table table1 (
  id int);
create table table2 (
  id int, value varchar(10));
insert table1 values (1),(2),(3),(4);
insert table2 values
(1,1),
(2,2),
(3,3),
(4,4);

DECLARE @id INT, @value VARCHAR(10);

SELECT @id=1, @value='';

SELECT
    @value = @value + (SELECT TOP 1 value FROM TABLE2 WHERE id=@id) + '-',
    @id = @id+1
FROM TABLE1;

select @value, @id

-- result
1-1-1-1       5

If you merely wanted the values from 2, then instead of the variable @id, you just correlate the subquery to the table.id as below:

create table table1 (id int);
create table table2 (id int, value varchar(10));
insert table1 values (1),(2),(3),(4);
insert table2 values
(1,1),
(3,9),
(4,4);

DECLARE @value VARCHAR(10);

SELECT @value='';

SELECT
    @value = @value + isnull((SELECT TOP 1 value 
                              FROM TABLE2 
                              WHERE id=table1.id) + '-','')
FROM TABLE1;

select @value

-- Result
1-9-4

Answer 3

It's because you are starting your @id at 0. Unless you have an id of 0 in your table, you probably need to do: SELECT @id=1

Alternately, you could condense it to this and not use the id:

SELECT @value = @value + value +  '-'
FROM TABLE2 t2
INNER JOIN TABLE1 t1 ON t1.id = t2.id