为 SELECT 运算符（MS SQL SERVER）执行像“表”一样的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9925143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute store procedure like a "table" for SELECT operator (MS SQL SERVER)
提问by Developer
Is it possible to execute store procedure
like a "table" for SELECToperator (MS SQL SERVER)?
是否可以为SELECT操作员（MS SQL SERVER）执行像“表”这样的存储过程？
Something like
就像是
SELECT TotalSum FROM exec MyStoreProcedure '2011/11/01', '2011/11/01'
I mean somehow integrate it into the SELECToperator?
我的意思是以某种方式将它集成到SELECT操作员中？
Thank you!
谢谢！
Thanks guys!
谢谢你们！
The solution what I did is based on your answers:
我所做的解决方案是基于您的回答：
declare @result table (f1 varchar(20),f2 varchar(20), CodProducto int, NomProducto varchar(1000), Costo decimal, Cantidat int, Total decimal)
INSERT INTO @result exec MyStoreProcedure '20111201', '20111201'
select * from @result
采纳答案by Diego
I supposed your proc returns several columns and you just want one, right?
我想你的 proc 会返回几列，而你只想要一列，对吧？
small workaround is to add the result of the proc to a table variable and then select from it
小的解决方法是将 proc 的结果添加到表变量中，然后从中选择
create proc proc1 as
select 1 as one, 2 as two
declare @result table (one int, two int)
insert into @result
exec proc1
select one from @result
回答by Vinnie
This would be better as a function rather than a stored procedure.
这作为一个函数而不是一个存储过程会更好。
create function dbo.TestTable
(@var1 bit)
returns table
AS
RETURN
( select *
from INFORMATION_SCHEMA.TABLES
where @var1 = 1
);
select * from
dbo.TestTable(1)
回答by Mitch Wheat
Not directly (or without altering the stored procedure to be a table-valued function).
不直接（或不将存储过程更改为表值函数）。
But you could do this:
但你可以这样做：
INSERT INTO SomeTempTableWithSchemaMatchingTheSproc (...)
EXEC MyStoredProcedure
SELECT * FROM SomeTempTableWithSchemaMatchingTheSproc
SQL Server 2005 onwards, you can also use a table variable.
从 SQL Server 2005 开始，还可以使用表变量。
回答by deni
This works for me:
这对我有用：
CREATE VIEW dbo.vw_xxx
AS
select * from openquery(YOURSERVERNAME, 'exec [sp_xxx] '''','''','''','''','''','''' ')
