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
Try ensuring that the string is not null at creation
Either
declare @strlist varchar(1000) = ''
OR
set @strlist = ISNULL(@strlist,'') + @itemstr + ','