SQL Server - SELECT FROM 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1492411/
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
SQL Server - SELECT FROM stored procedure
提问by jonathanpeppers
I have a stored procedure that returns rows:
我有一个返回行的存储过程：
CREATE PROCEDURE MyProc
AS
BEGIN
SELECT * FROM MyTable
END
My actual procedure is a little more complicated, which is why a sproc is necessary.
我的实际过程稍微复杂一些，这就是为什么需要 sproc。
Is it possible to select the output by calling this procedure?
是否可以通过调用此过程来选择输出？
Something like:
就像是：
SELECT * FROM (EXEC MyProc) AS TEMP
I need to use SELECT TOP X, ROW_NUMBER, and an additional WHEREclause to page my data, and I don't really want to pass these values as parameters.
我需要使用SELECT TOP X,ROW_NUMBER和一个额外的WHERE子句来分页我的数据，我真的不想将这些值作为参数传递。
采纳答案by Mehrdad Afshari
You can use a User-defined functionor a viewinstead of a procedure.
A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECTstatement.
一个过程可以返回多个结果集，每个结果集都有自己的模式。不适合在SELECT语句中使用。
回答by Charles Bretana
You can
你可以
- create a table variable to hold the result set from the stored proc and then
- insert the output of the stored proc into the table variable, and then
- use the table variable exactly as you would any other table...
- 创建一个表变量来保存来自存储过程的结果集，然后
- 将存储过程的输出插入到表变量中，然后
- 完全像使用任何其他表一样使用表变量...
... sql ....
... sql ....
Declare @T Table ([column definitions here])
Insert @T Exec storedProcname params
Select * from @T Where ...
回答by CMerat
You either want a Table-Valued functionor insert your EXEC into a temporary table:
您要么想要一个表值函数，要么将您的 EXEC 插入到临时表中：
INSERT INTO #tab EXEC MyProc
回答by Rizwan Mumtaz
You must read about OPENROWSETand OPENQUERY
你必须阅读关于OPENROWSET和OPENQUERY
SELECT *
INTO #tmp FROM
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
回答by Aamir
You need to declare a table type which contains the same number of columns your store procedure is returning. Data types of the columns in the table type and the columns returned by the procedures should be same
您需要声明一个包含与您的存储过程返回的列数相同的表类型。表类型中列的数据类型与程序返回的列的数据类型应相同
declare @MyTableType as table
(
FIRSTCOLUMN int
,.....
)
Then you need to insert the result of your stored procedure in your table type you just defined
然后你需要在你刚刚定义的表类型中插入你的存储过程的结果
Insert into @MyTableType
EXEC [dbo].[MyStoredProcedure]
In the end just select from your table type
最后只需从您的表格类型中选择
Select * from @MyTableType
回答by DavideDM
It is not necessary use a temporary table.
没有必要使用临时表。
This is my solution
这是我的解决方案
SELECT * FROM
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
WHERE somefield = anyvalue
回答by DavideDM
You can copy output from sp to temporaty table.
您可以将输出从 sp 复制到临时表。
CREATE TABLE #GetVersionValues
(
[Index] int,
[Name] sysname,
Internal_value int,
Character_Value sysname
)
INSERT #GetVersionValues EXEC master.dbo.xp_msver 'WindowsVersion'
SELECT * FROM #GetVersionValues
drop TABLE #GetVersionValues
回答by Ali asghar Fendereski
use OPENQUERY and befor Execute set 'SET FMTONLY OFF; SET NOCOUNT ON;'
使用 OPENQUERY 和之前执行 set 'SET FMTONLY OFF; 设置无计数；'
Try this sample code:
试试这个示例代码：
SELECT top(1)*
FROM
OPENQUERY( [Server], 'SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE [database].[dbo].[storedprocedure] value,value ')
回答by Ali Osman Yavuz
If 'DATA ACCESS' false,
如果“数据访问”为假，
EXEC sp_serveroption 'SQLSERVERNAME', 'DATA ACCESS', TRUE
after,
后，
SELECT * FROM OPENQUERY(SQLSERVERNAME, 'EXEC DBNAME..MyProc @parameters')
it works.
有用。
回答by al_the_man
Try converting your procedure in to an Inline Function which returns a table as follows:
尝试将您的过程转换为内联函数，该函数返回一个表，如下所示：
CREATE FUNCTION MyProc()
RETURNS TABLE AS
RETURN (SELECT * FROM MyTable)
And then you can call it as
然后你可以称之为
SELECT * FROM MyProc()
You also have the option of passing parameters to the function as follows:
您还可以选择将参数传递给函数，如下所示：
CREATE FUNCTION FuncName (@para1 para1_type, @para2 para2_type , ... )
And call it
并称之为
SELECT * FROM FuncName ( @para1 , @para2 )
