Hi,
I need to access result of a stored procedure withinf a select statement (ie : SELECT * FROM [dbo].[sp_sample]) in SQL_Server 2005.
Regards,
Stéphane
+1
A:
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.
Barry
2010-03-25 13:43:57
in fact, my select statement is into a view ... any ideaI use a SP because I need a temporary table.
Stéphane Schwartz
2010-03-25 14:35:37
A:
@Barry is right you need to create a temp table and insert into it first, then join that table in your 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
KM
2010-03-25 14:02:24