I need to access the result of a stored procedure within a select statement, i.e.:
我需要在select语句中访问存储过程的结果,例如:
SELECT * FROM [dbo].[sp_sample]
in SQL_Server 2005.
2005年SQL_Server。
2 个解决方案
#1
1
@Barry is right you need to create a temp table and insert into it first, then join that table in your select.
@Barry是对的,您需要创建一个临时表并首先插入到其中,然后在select中加入该表。
However, there are numerous ways for sharing data between stored procedures, see this excellent article: How to Share Data Between Stored Procedures by Erland Sommarskog
但是,在存储过程之间共享数据有很多方法,请参阅这篇优秀的文章:如何在存储过程之间共享数据
One method that may work for you is to "share" a temp table. The #temp table is created in the Parent procedure and can be used by the Child: http://www.sommarskog.se/share_data.html#temptables
一个可能对您有用的方法是“共享”一个临时表。#temp表是在父进程中创建的,可以由Child使用:http://www.sommarskog.se/share_data.html#temptables。
#2
3
This isn't possible. You would have to create a temporary table to store the results.
这是不可能的。您必须创建一个临时表来存储结果。
Create Table #tmp
(
...
)
Insert into #tmp
Exec dbo.StoredProcedure
The table structure must match the output of the Stored Procedure.
表结构必须与存储过程的输出匹配。
#1
1
@Barry is right you need to create a temp table and insert into it first, then join that table in your select.
@Barry是对的,您需要创建一个临时表并首先插入到其中,然后在select中加入该表。
However, there are numerous ways for sharing data between stored procedures, see this excellent article: How to Share Data Between Stored Procedures by Erland Sommarskog
但是,在存储过程之间共享数据有很多方法,请参阅这篇优秀的文章:如何在存储过程之间共享数据
One method that may work for you is to "share" a temp table. The #temp table is created in the Parent procedure and can be used by the Child: http://www.sommarskog.se/share_data.html#temptables
一个可能对您有用的方法是“共享”一个临时表。#temp表是在父进程中创建的,可以由Child使用:http://www.sommarskog.se/share_data.html#temptables。
#2
3
This isn't possible. You would have to create a temporary table to store the results.
这是不可能的。您必须创建一个临时表来存储结果。
Create Table #tmp
(
...
)
Insert into #tmp
Exec dbo.StoredProcedure
The table structure must match the output of the Stored Procedure.
表结构必须与存储过程的输出匹配。