Using the result of an Stored procedure in a Select statement

Question

i have an stored procedure wich return a Dataset(Table) how i can use the result of this stored procedure in a select statement?

i need something like this

Select T1.* from  Dummy T0
INNER JOIN
(EXEC [dbo].[SPGetResults] '900',300,'USD') T1 ON T1.aKey=T0.aKey

i ' am using sql server 2005

Answer 1

Create a table-valued user-defined function instead.

Answer 2

Use Insert Into ... Exec and store the result into a Temp Table... Then you can join the Temp table in your select statement.

Alternatively as suggested before try converting the SP into a Table valued function.

This link provides much more options for you... http://www.sommarskog.se/share_data.html

Answer 3

i agree with marcelo mostly, but if you are set on using a stored procedure, or your stored procedure does anything that affects data, you could create a #temp table with the structure of the output of your stored procedure, and then do something like

insert into #temp
exec [dbo].[SPGetResults] '900',300,'USD'

and then do your joins and selects on the temp table

Answer 4

Hi the answer of Marcelo Cantos is the best one. also for distributed queries you can use the following script:

USE [master]

sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE

USE [YourDB]

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=YourServer ;Trusted_Connection=yes;', 'EXEC YourDB.YourSchema.YourSP ''YourParameters1'', YourParameters2') AS c INNER JOIN YourTableOrView ap ON ap.YourPK = c.YourFK

http://www.kodyaz.com/articles/how-to-sql-select-from-stored-procedure-using-openquery-openrowset.aspx