假设有表:
CREAET TABLE a(id int, name varchar(50))
INSERT INTO a SELECT 1, 'AAAA'
INSERT INTO a SELECT 2, 'BBBB'
存储过程:
CREAETE PROC usp_fetch_a
ASSELECT * FROM a
方法一:
CREATE TABLE #tmp(id int, name varchar(50))
INSERT INTO #tmp EXEC usp_fetch_a
DROP TABLE #tmp
【此处不能用表变量】
方法二:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','ServerName';'Login';'Passwd','exec Database.dbo.usp_fetch_a') AS a
【注意Login前后是分号,不是逗号】