CODEWITHSUNDEEP
t-sqlstored-proceduresconcatenation
Gaël Info
Gaël Info

Reputation: 3

How to concat two vars in while loop

I'd like to construct a 'union all' query with a while loop.

I've already tried += concatenation but doesn't work.

DECLARE @cnt1 int , @concat nvarchar(max), @qry nvarchar(500);
SET @cnt1 = 1;
WHILE @cnt1 < 99 
BEGIN 
SET @qry = 'select name_' + CAST(@cnt1 AS CHAR) + ' , name2_' + CAST(@cnt1 AS CHAR) + ', m.state1 FROM table1 P left join table2 M on M.name = P.name_' + CAST(@cnt1 AS CHAR) + ' where p.nb > 1'; 
SET @cnt1 = @cnt1 + 1; 
SET @concat += ' UNION ALL ' + @qry 
END 
EXEC sp_executesql @concat

@concat is still empty at the end of the loop...

Thank you vm

Upvotes: 0

Views: 28

Answers (1)

HABO
HABO

Reputation: 15816

Since @concat isn't initialized, the default value is null. Concatenating a value with null results in null, so no progress is made in your loop. Initializing it to an empty string:

declare @Concat as NVarChar(max) = N'';

will fix the problem.

Tip: The default length of a Char, VarChar, NChar or NVarChar is one character most of the time. When it is the target type of a Cast or Convert then it is 30 characters. Best practice: Always specify a length.

Upvotes: 2

Related Questions