SELECT * FROM OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server};SERVER=ServerName;UID=sa;PWD=', 'exec DBName..ProcName') AS a
select * from syscolumns where isoutparam = 1 and id in (select [id] from objects where [name] = 'you procedure' and xtype = 'p')if @@rowcount <> 0 begin print '有返回值' end
--判断是否有结果,如果有的话列出所有字段,待优化IF EXISTS(SELECT name FROM sysobjects WHERE name = N'testtable' AND type = 'U') DROP TABLE testtable GO if exists(SELECT * FROM OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server};SERVER=server;UID=sa;PWD=', 'exec ft_xs..bbb') AS a) begin SELECT * into testtable FROM OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server};SERVER=server;UID=sa;PWD=', 'exec ft_xs..bbb') AS aexec sp_columns 'testtable' end
FROM OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=ServerName;UID=sa;PWD=',
'exec DBName..ProcName') AS a
where isoutparam = 1
and id in (select [id] from objects
where [name] = 'you procedure' and xtype = 'p')if @@rowcount <> 0
begin
print '有返回值'
end
FROM sysobjects
WHERE name = N'testtable'
AND type = 'U')
DROP TABLE testtable
GO
if exists(SELECT *
FROM OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=server;UID=sa;PWD=',
'exec ft_xs..bbb') AS a)
begin
SELECT * into testtable
FROM OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=server;UID=sa;PWD=',
'exec ft_xs..bbb') AS aexec sp_columns 'testtable'
end
对于有输入参数的存储过程,当然要输入参数进行模拟来获得数据集的查询结果.
因为有的存储过程在输入不同参数时,有可能是结果集,也可能是返回值,所以必须输入参数确定
as
select 1 --虽然返回的是结果集,但没有指定列名,一样会出错.