Using Stored Procedure into Select (T-SQL)

I need to access the result of a stored procedure within a开发者_JAVA技巧 select statement, i.e.:

SELECT * FROM [dbo].[sp_sample]

in SQL_Server 2005.

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 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