SQL Server -从存储过程中选择。

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子句来页面我的数据，我并不想把这些值作为参数传递。

15 个解决方案

#1

112

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语句中使用。

#2

132

You should look at this excellent article by Erland Sommarskog:

你应该看看Erland Sommarskog的这篇优秀文章:

How to Share Data Between Stored Procedure
如何在存储过程之间共享数据

It basically lists all available options for your scenario.

它基本上列出了您的场景的所有可用选项。

#3

123

You can

你可以

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

You either want a Table-Valued function or insert your EXEC into a temporary table:

您要么需要一个表值函数，要么将EXEC插入到临时表中:

INSERT INTO #tab EXEC MyProc

#5

You must read about OPENROWSET and OPENQUERY

您必须阅读有关OPENROWSET和OPENQUERY的内容

SELECT  * 
INTO    #tmp FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')

#6

It is not necessary use a temporary table.

不需要使用临时表。

This is my solution

这是我的解决方案

SELECT  *  FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
WHERE somefield = anyvalue

#7

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

#8

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

#9

use OPENQUERY and befor Execute set 'SET FMTONLY OFF; SET NOCOUNT ON;'

使用OPENQUERY和befor执行set ' set FMTONLY OFF;设置NOCOUNT;”

Try this sample code:

试试这个示例代码:

SELECT top(1)*
FROM
OPENQUERY( [Server], 'SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE  [database].[dbo].[storedprocedure]  value,value ')

#10

You can cheat a little with OPENROWSET :

你可以用OPENROWSET作弊一下:

SELECT ...fieldlist...
FROM OPENROWSET('SQLNCLI', 'connection string', 'name of sp')
WHERE ...

This would still run the entire SP every time, of course.

当然，这仍然会每次运行整个SP。

#11

If 'DATA ACCESS' false,

如果数据访问的假,

EXEC sp_serveroption 'SQLSERVERNAME', 'DATA ACCESS', TRUE

after,

之后,

SELECT  *  FROM OPENQUERY(SQLSERVERNAME, 'EXEC DBNAME..MyProc @parameters')

it works.

它的工作原理。

#12

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 )

#13

For the sake of simplicity and to make it re-runnable, I have used a system StoredProcedure "sp_readerrorlog" to get data:

为了简单起见，我使用了一个系统存储过程sp_readerrorlog来获取数据:

-----USING Table Variable
DECLARE @tblVar TABLE (
   LogDate DATETIME,
   ProcessInfo NVARCHAR(MAX),
   [Text] NVARCHAR(MAX)
)
INSERT INTO @tblVar Exec sp_readerrorlog
SELECT LogDate as DateOccured, ProcessInfo as pInfo, [Text] as Message FROM @tblVar



-----(OR): Using Temp Table
IF OBJECT_ID('tempdb..#temp') IS NOT NULL  DROP TABLE #temp;
CREATE TABLE #temp (
   LogDate DATETIME,
   ProcessInfo NVARCHAR(55),
   Text NVARCHAR(MAX)
)
INSERT INTO #temp EXEC sp_readerrorlog
SELECT * FROM #temp

#14

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.

听起来你可能只需要使用视图。视图允许将查询表示为表，以便查询视图。

#15

If your server is called SERVERX for example, this is how I did it...

例如，如果您的服务器名为SERVERX，我就是这样做的……

EXEC sp_serveroption 'SERVERX', 'DATA ACCESS', TRUE;
DECLARE @CMD VARCHAR(1000);
DECLARE @StudentID CHAR(10);
SET @StudentID = 'STUDENT01';
SET @CMD = 'SELECT * FROM OPENQUERY([SERVERX], ''SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE MYDATABASE.dbo.MYSTOREDPROC ' + @StudentID + ''') WHERE SOMEFIELD = SOMEVALUE';
EXEC (@CMD);

To check this worked, I commented out the EXEC() command line and replaced it with SELECT @CMD to review the command before trying to execute it! That was to make sure all the correct number of single-quotes were in the right place. :-)

为了检查这个工作，我注释了EXEC()命令行，并在尝试执行它之前，用SELECT @CMD来替换它!这是为了确保所有正确的单引号数量都在正确的位置。:-)

I hope that helps someone.

我希望这能帮助某人。

#1

112