Using the result of a stored procedure in a Select statement

Question

I have a stored procedure that returns Dataset(Table). How can I use the result of this stored procedure in an instruction SELECT?

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

Instead, create a function defined by the table.

Answer 2

, -, , #temp -

INSERT INTO #temp
EXEC [dbo].[SPGetResults] '900',300,'USD'

.

Answer 3

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

Into... Exec Temp... Temp select.

Alternatively, as suggested, before trying to convert the SP to 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 a User Defined Value table:

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