Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

closed as off-topic by Mikael Eriksson, Paul White, ypercube, Mark Storey-Smith, Kin Jun 12 at 13:41

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mikael Eriksson, Paul White, ypercube, Mark Storey-Smith, Kin
If this question can be reworded to fit the rules in the help center, please edit the question.

3  
@s is null from the start and if you concatenate something null with anything else the result is null. Initialize @s to an empty string before the loop. –  Mikael Eriksson Jun 12 at 5:25
    
What version of SQL Server are you using? –  Martin Smith Jun 12 at 8:52

2 Answers 2

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;
share|improve this answer

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.