Convert column values into string

Question

I'm trying to write e proc which will convert column values into a string separated with a character: ',' for example. it stucks on this line exec @maxcount = sp_executesql @temp and returns the value of @maxxount any suggestions how could I set a value of dynamic query execution so that it continued processing all the function an executed the correct answer.

ALTER procedure [dbo].[usp_stringConvert](
 @table_name varchar(101),
 @column_name varchar(100),
 @separator varchar(20)
 )
as 

declare @maxcount int, @temp nvarchar(1000)
declare @count int
declare @result varchar(1000)
set @result =''
set @count =1
set @temp= Concat('select count(', @column_Name ,') from ', @table_name)
exec @maxcount =  sp_executesql @temp
while (@count<@maxcount)
begin
    if @count!=1
        set @result+=@separator
    set @temp=Concat('select ' , @column_name ,' from ' , @table_name , 'where @count = row_number() over(order by (select (100)))')
    exec @temp = sp_executesql @temp
    set @result =CONCAT(@result, @temp)
    set @count+=1;
end
select @result;

Did you search before posting? I guess there is many answers at least here on SO to this question. [That is one of many](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Ilyes, Jul 14 '19 at 12:59
Not sure exactly what you're actually trying to achieve here, if I'm honest. Sample data and expected results will help here. Also, you should really consider properly quoting your objects for dyamnamic SQL by use of `QUOTENAME` and using the correct data type (`sysname`); this is just an injection vulnerability waiting to be exploited at the moment I'm afraid. — Thom A, Jul 14 '19 at 13:05
Possible duplicate of [Convert column to string in SQL Select](https://stackoverflow.com/questions/15299241/convert-column-to-string-in-sql-select) — Kate Orlova, Jul 14 '19 at 13:10

score 1 · Answer 1 · answered Jul 14 '19 at 13:19

Using regular SQL in SQL Server, you can concatenate strings using:

select stuff( (select concat(separator, column_name)
               from table_name
               for xml path ('', type)
              ).value('.', nvarchar(max)
                     ), 1, len(separator), ''
             )

You should be able to turn this into dynamic SQL using:

declare @sql nvarchar(max);
declare @result nvarchar(max);

set @sql = N'
select @result = stuff( (select concat(@separator + [column_name]
                         from [table_name]
                         for xml path ('''', type)
                        ).value(''.'', nvarchar(max)
                               ), 1, len(@separator), ''
                      )
';

set @sql = replace(@sql, '[table_name]', quotename(@table_name));
set @sql = replace(@sql, '[column_name]', quotename(@column_name));

exec sp_executesql @sql,
                   N'@result nvarchar(max) output',
                   @result=@result output;

select @result;

Convert column values into string

1 Answers1