Using a SP result set in a FROM clause

Bernard Omiple · October 2nd, 2003, 06:32 PM

Hi all:

For example, I have an SP (stored proc) named sp_getinvoices that returns a result set. How can i use this SP in a select statement?

Like...
select * from execute("sp_getinvoices") where invdate > '01/01/2003'

invdate is a field in the result set of the sp that we can get by simply issuing execute sp_getinvoices.

I know that statement won't work, but this could give us an idea what I want to achieve.

TIA...
Bernard

pgtips · October 3rd, 2003, 05:38 AM

You can do this by:
1. creating a temporary table which matches the structure of the "table" that the sp returns, then
2. use an INSERT EXEC statement to put the data into the temporary table, then
3. do a SELECT against the temporary table including your WHERE clause

Here's an example using one of the stored procs in Northwind:

Code:

CREATE TABLE #tmp1
(  ProductName nvarchar(40),
   UnitPrice money)

INSERT #tmp1
EXEC [Ten Most Expensive Products]

SELECT * FROM #tmp1 WHERE UnitPrice < 100

DROP TABLE #tmp1

hth
Phil