combining results from 2 sproc calls

[bdotzour]

This may be hopeless, but what I want to do is essentially call a sproc 2 times, each time with different parameters, and then AND/OR the result sets.

What I'd love to do is this:

select * from table where id in
( sproc 'TX' union sproc 'CA' )

Any ideas at all?

 

[tlbroadbent]

It's not possible to do as you coded. However, you can do the following. Create a temporary table to hold the output of the SP. Insert the output of the SP into the table. Select from the table.

Create table #t (col1 datatype, col2 datatype, ...)

Insert Into #t Exec ('sproc ''TX''')
Insert Into #t Exec ('sproc ''CA''')

Select * From #t

Drop table #t Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[fluteplr]

Or if you are running SQL 2000. Recode the stored procs as user functions that return tables.