SQL Server 取得exec执行结果中的值怎么取？

Question

declare @sql varchar(2000), @tsql nvarchar(max), @split varchar(100)
set @sql='A,B,C,D,E' --保存的字符
set @split=','--分隔符
select @tsql='select ''' + replace(@sql,@split,''' AS New union all select ''') + ''' '
exec(@tsql)



create table T(id char(6), words varchar(100))
insert into T values('1', 'A,B,C,D,E')
--drop table T

declare @tsql varchar(100), @sentence varchar(100)
exec('select top 1 words from T where id=''1'' ')

--此处本尝试获得exec执行的结果(字符串),以用于下面的Replace函数第一个参数,但未找到方法;

select @tsql='select ''' + replace('A,B,C,D,E',',',''' AS New union all select ''') + ''' '
exec(@tsql)

Answer 1

这里可以不用EXEC，换另外一种思路。

DECLARE @word VARCHAR(100);
SELECT @word = (SELECT TOP 1 words
                  FROM T
                 WHERE id = 1);

这样就可以获取到字符串了。

Answer 2

declare @sql varchar(2000), @tsql nvarchar(max), @split varchar(100)
set @sql='A,B,C,D,E' --保存的字符
set @split=','--分隔符
select @tsql='select ''' + replace(@sql,@split,''' AS New union all select ''') + ''' '

declare @t table([new] nvarchar(255))
insert into @t exec(@tsql)
select * from @t

但正如上面所说的，这里没必要用exec，直接取值就可以

declare @t table(id char(6), words varchar(100))
insert into @t values('1', 'A,B,C,D,E')
insert into @t values('2', 'A,B,C,D,E,F')
insert into @t values('3', 'A,B,C,D,E,F,G')

declare @w varchar(100)
select top 1 @w=words from @t

declare @tsql varchar(2000)
select @tsql='select ''' + replace(@w,',',''' AS New union all select ''') + ''' '
exec(@tsql)