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

Question

declare@sqlvarchar(2000),@tsqlnvarchar(max),@splitvarchar(100)
set@sql='A,B,C,D,E'--保存的字符
set@split=','--分隔符
select@tsql='select'''+replace(@sql,@split,'''ASNewunionallselect''')+''''
exec(@tsql)
createtableT(idchar(6),wordsvarchar(100))
insertintoTvalues('1','A,B,C,D,E')
--droptableT
declare@tsqlvarchar(100),@sentencevarchar(100)
exec('selecttop1wordsfromTwhereid=''1''')
--此处本尝试获得exec执行的结果(字符串),以用于下面的Replace函数第一个参数,但未找到方法;
select@tsql='select'''+replace('A,B,C,D,E',',','''ASNewunionallselect''')+''''
exec(@tsql)

Answer 1

这里可以不用EXEC，换另外一种思路。DECLARE@wordVARCHAR(100);SELECT@word=(SELECTTOP1wordsFROMTWHEREid=1);这样就可以获取到字符串了。

Answer 2

declare@sqlvarchar(2000),@tsqlnvarchar(max),@splitvarchar(100)set@sql='A,B,C,D,E'--保存的字符set@split=','--分隔符select@tsql='select'''+replace(@sql,@split,'''ASNewunionallselect''')+''''declare@ttable([new]nvarchar(255))insertinto@texec(@tsql)select*from@t但正如上面所说的，这里没必要用exec，直接取值就可以declare@ttable(idchar(6),wordsvarchar(100))insertinto@tvalues('1','A,B,C,D,E')insertinto@tvalues('2','A,B,C,D,E,F')insertinto@tvalues('3','A,B,C,D,E,F,G')declare@wvarchar(100)selecttop1@w=wordsfrom@tdeclare@tsqlvarchar(2000)select@tsql='select'''+replace(@w,',','''ASNewunionallselect''')+''''exec(@tsql)