0

I using the procedure below, I am trying to concatenate the returned itemstr to the variable strlist with commas between the values. I get the individual itemstr values with each iteration of the look, but strlist is always null.

   alter procedure my_test

   as 

   declare @counter int
   declare @columnstr varchar(100)
   declare @itemstr varchar(100)
   declare @strlist varchar(1000)

   declare @sqlstr nvarchar(1000)

   set @counter = 1


   while @counter < 15
   begin
     set @columnstr = 'txt_item' + ltrim(cast(@counter as varchar(2)))
     --select @columnstr as columnstr

     set @sqlstr = 'select @outstr = ' + @columnstr + ' from group1_ where id = ''298'''
     --select @sqlstr

   execute sp_executesql @sqlstr,N'@outstr varchar(100) OUTPUT',   @outstr=@itemstr OUTPUT;
   select @itemstr as itemstr

    set @strlist = @strlist + @itemstr + ','
    select @strlist as strlist  -- this is always null

    set @counter = @counter + 1
   end
   select @strlist     -- this is always null
   go
Dean-O
  • 1,143
  • 4
  • 18
  • 35
  • You get NULL because you concatenate using +, if any of terms is NULL entire string is NULL. Concat using `CONCAT` (SQL Server 2012+) or use `+ ISNULL(@value, default_value) +` or `+ COALESCE(@value, default_value) +` – Lukasz Szozda Aug 29 '15 at 17:24

1 Answers1

1

Try ensuring that the string is not null at creation

Either 

 declare @strlist varchar(1000) = ''

OR

set @strlist = ISNULL(@strlist,'') + @itemstr + ','
Richard Vivian
  • 1,700
  • 1
  • 14
  • 19