Search code examples
t-sqlstring-concatenationsp-executesql

how can I concatenate looped sp_executesql output into a variable


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

Solution

  • Try ensuring that the string is not null at creation

    Either

     declare @strlist varchar(1000) = ''
    

    OR

    set @strlist = ISNULL(@strlist,'') + @itemstr + ','