select * into #tbtest from tb_test where 1<>1 --先建个返回记录集一样结构的临时表 insert into #tbtest exec pr_rsTest--把返回的结果报错到临时表里select * from #tbtest--对临时表操作 drop table #tbtestCreate PROCEDURE pr_rsTest AS Declare @SQL nvarchar(4000) Set @SQL='select * from tb_test' -- @SQL是根据传入参数动态组合的,此仅为示例 exec (@SQL) --如果以EXEC方式执行,总是不能返回数据集
create table #(col varchar(500)) insert # exec master..xp_cmdshell 'dir *.txt' select right(col,len(col)-38) as filename from # where isnumeric(left(col,1))= 1/*filename ----------- eula.txt sfmuam.txt iisperf.txt BsLogo.txt */
select * into #tt from tb where 1<>1 insert into #tt exec test select * from #tt drop table #tt
exec 存储过程
select * into #
from openrowset('sqloledb','ip';'sa';'','set fmtonly off;exec test(参数)')
truncate table a--先清除表的内容 第一次执行就不加这个
insert into a( ,)
select * from b
create table #yourTempdb(返回的表结构)
insert into #yourTempdb exec 你的存储过程
##tableName -------------〉全局临时表
insert into #tbtest exec pr_rsTest--把返回的结果报错到临时表里select * from #tbtest--对临时表操作
drop table #tbtestCreate PROCEDURE pr_rsTest
AS
Declare @SQL nvarchar(4000)
Set @SQL='select * from tb_test'
-- @SQL是根据传入参数动态组合的,此仅为示例
exec (@SQL) --如果以EXEC方式执行,总是不能返回数据集
insert # exec master..xp_cmdshell 'dir *.txt'
select right(col,len(col)-38) as filename from # where isnumeric(left(col,1))= 1/*filename
-----------
eula.txt
sfmuam.txt
iisperf.txt
BsLogo.txt
*/
insert into #tt exec test
select * from #tt
drop table #tt