Why Subquery Inside Recursive String Concatenation Always Returns Null?

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

Solution 1:

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.

createtable table1 (
  id int);
createtable table2 (
  id int, valuevarchar(10));
insert table1 values (1),(2),(3),(4);
insert table2 values
(1,1),
(2,2),
(3,3),
(4,4);

DECLARE@idINT, @valueVARCHAR(10);

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

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

select@value, @id-- result1-1-1-15

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:

createtable table1 (id int);
createtable table2 (id int, valuevarchar(10));
insert table1 values (1),(2),(3),(4);
insert table2 values
(1,1),
(3,9),
(4,4);

DECLARE@valueVARCHAR(10);

SELECT@value='';

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

select@value-- Result1-9-4

Solution 2:

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
INNERJOIN TABLE1 t1 ON t1.id = t2.id

Solution 3:

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

declare@idint, @valuevarchar(max);
select@id=0, @value='';

select@value=@value+ isnull(value,'') +casewhenvalueisnullthen''else'-'endfrom 
    table2 t2
where 
    t2.id in (select id from table1)