Using the result of an Stored procedure in a Select statement

Question

I have a stored procedure which returns a Dataset(Table). How can I 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'm using SQL Server 2005

Answer 1

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 2

Create a table-valued user-defined function instead.

Answer 3

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

Answer 4

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 5

Here is a simple example of table Value user-defined function :

create function personSidsByLastName(@lastName varchar(20))
returns table
as 
return 
select personSid from Person where lastName like @lastName

select * from PersonAddress pa where pa.personSid in (select personSid from personSidsByLastName('ali'))