join table with procedure?

Is it possible to join the results of a stored procedure with a table?
SELECT field1, field2, field3 FROM tblCustomer INNER JOIN (exec spTest
'inputvar' AS SP) ON tblCustomer.CustID = SP.CustID
Thanks,
KeithKeith G Hicks,
You need to grab the result of the sp into a table (permanent or temporal)
and use the table in the join, or you can re-write the sp as a table functio
n.
create table #t1(c1 ...)
insert into #t1(c1, ...)
exec spTest 'inputvar'
SELECT field1, field2, field3
FROM tblCustomer INNER JOIN #t1 AS SP ON tblCustomer.CustID = SP.CustID
drop table #t1
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Keith G Hicks" wrote:

> Is it possible to join the results of a stored procedure with a table?
> SELECT field1, field2, field3 FROM tblCustomer INNER JOIN (exec spTest
> 'inputvar' AS SP) ON tblCustomer.CustID = SP.CustID
> Thanks,
> Keith
>
>|||Cool. That's what I'm doing. I just wasn't sure if there was a way to join
directly to the procedure. Thanks for the info.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8D7D36E8-8EF7-4E4B-8613-59F8BC64148C@.microsoft.com...
Keith G Hicks,
You need to grab the result of the sp into a table (permanent or temporal)
and use the table in the join, or you can re-write the sp as a table
function.
create table #t1(c1 ...)
insert into #t1(c1, ...)
exec spTest 'inputvar'
SELECT field1, field2, field3
FROM tblCustomer INNER JOIN #t1 AS SP ON tblCustomer.CustID = SP.CustID
drop table #t1
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Keith G Hicks" wrote:

> Is it possible to join the results of a stored procedure with a table?
> SELECT field1, field2, field3 FROM tblCustomer INNER JOIN (exec spTest
> 'inputvar' AS SP) ON tblCustomer.CustID = SP.CustID
> Thanks,
> Keith
>
>

join results from multiple EXEC calls

I would like to make inner join with results from different exec %procedureName% calls.

thanks.

insert into #temp
exec Proc1

insert into #temp2
exec Proc2

insert into #temp3
exec Proc3

Then join or union the tables (depending on the data)
If the reultsets are the same you can insert into 1 table instead of multiple tables

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You might also consider rewriting the procedures as functions if it is possible, then the join of the sets would be very natural.

select *
from function (parm) as set1
join function2(parm) as set2
on set1.value = set2.value