如果能够方便的得到存储过程结果集的表结构。那存储过程的使用就方便了很多了。比如:
insert into #tmp exec sp_who
要执行这一句,前提必须知道sp_who的结果集的表结构。
这样的语句又不能运行:select * into #tmp from exec sp_who
解答:
1:
select * into #z from OPENROWSET(
'SQLOLEDB',
'SERVER=server;uid=sa;pwd=sapwd;Database=master','exec sp_who') as a
2: 如果存储过程中用到临时表,要用set fmtonly off
select * into #z from OPENROWSET(
'SQLOLEDB',
'SERVER=server;uid=sa;pwd=sapwd;Database=master','SET FMTONLY OFF; exec 存储过程名') as a
两种方法:
1.先建表再执行存储过程:
create table #t(...)
insert into #t exec B
select * from #t
drop table #t
2.直接select into到临时表:
select * into #t from OPENROWSET(
'SQLOLEDB','SERVER=servername;uid=sa;pwd=123;Database=testdb',
'SET FMTONLY OFF;set nocount on;exec B') as a
select * from #t
drop table #t