NULL value returned when concatenating a string in SQL while loop! [closed]

Question

I'm trying concatenate a string in a while loop, but it only returns NULL. Is there something I'm missing?

declare @o int = 10000000/1000000;
declare @i int = 0;
declare @s nvarchar(200);

while @i < @o
  begin
    set @s += 'hello' + cast(@i as nchar(4));
    set @i += 1;
  end

select @s;

Answer 1

If you are on SQL Server 2012+ you can use CONCAT. This treats NULL as an empty string.

declare @o int = 10000000/1000000;
declare @i int = 0;
declare @s nvarchar(200);

while @i < @o
  begin
    set @s = CONCAT(@s, 'hello', cast(@i as nchar(4)));
    set @i += 1;
  end

select @s;

Answer 2

Like the comment under your question suggests, concatenating a value to NULL will always yield NULL.

To get the value you are looking for, you need to initialise @s to a value:

declare @s nvarchar(200) = '';

The full query is like so:

declare @o int = 10000000/1000000;
declare @i int = 0;
declare @s nvarchar(200) = '';

while @i < @o
  begin
    set @s += 'hello' + cast(@i as nchar(4));
    set @i += 1;
  end

select @s;

And this gives me the answer:

hello0   hello1   hello2   hello3   hello4   hello5   hello6   hello7   hello8   hello9   

Whilst there is an option to turn off CONCAT_NULL_YIELDS_NULL, which will give you the answer you want without having to initialise the variable @s, this feature is deprecated and could have a knock-on effect on other queries (if turned off at the database level). It is also not advisable to use this in new development:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.