SQL Server - SELECT FROM存储过程

本文翻译自：SQL Server - SELECT FROM stored procedure

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 WHERE clause to page my data, and I don't really want to pass these values as parameters. 我需要使用SELECT TOP X ， ROW_NUMBER和一个额外的WHERE子句来分页我的数据，我真的不想将这些值作为参数传递。

---

#1楼

参考：https://stackoom.com/question/6GF9/SQL-Server-SELECT-FROM存储过程

---

#2楼

您要么需要表值函数，要么将EXEC插入临时表：

INSERT INTO #tab EXEC MyProc

---

#3楼

You can 您可以

1. create a table variable to hold the result set from the stored proc and then 创建一个表变量来保存存储过程中的结果集然后
2. insert the output of the stored proc into the table variable, and then 将存储过程的输出插入表变量，然后
3. 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 ...

---

#4楼

It sounds like you might just need to use a view . 听起来你可能只需要使用一个视图 。 A view allows a query to be represented as a table so it, the view, can be queried. 视图允许将查询表示为表，以便可以查询该视图。

---

#5楼

You can copy output from sp to temporaty table. 您可以将sp的输出复制到temporaty表。

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

---

#6楼

You can use a User-defined function or a view instead of a procedure. 您可以使用用户定义的函数或视图而不是过程。

A procedure can return multiple result sets, each with its own schema. 过程可以返回多个结果集，每个结果集都有自己的模式。 It's not suitable for using in a SELECT statement. 它不适合在SELECT语句中使用。