: We have an existing sproc (which we should NOT modify in any way: it
: is maintained by another project), that returns a result set.
: We now want to call that sproc from another sproc and use the result
: set for additional querying (it needs to be joined).
: Is it possible to direct the result set into a temp table (from the
: calling sproc!), or do we have to modify the original sproc so it
: directs its result set to a (predefined) temp table?
It is possible in a >= 11.5 server, through CIS (Component Integration
Services).
You can bind the result set of a procedure to a read-only table and
then use that table to insert the result set into a temp table (if that's
still needed then).
you have to enable cis and rpc with sp_configure.
Example (without parameters) :
use tempdb
go
create procedure num_proc
as
create table #localtable (i int)
insert into #localtable values (1)
insert into #localtable values (2)
insert into #localtable values (3)
insert into #localtable values (4)
insert into #localtable values (5)
select * from #localtable
drop table #localtable
return 0
go
sp_addobjectdef 'tempdb..numbers', 'DST_ST_1_C.tempdb..num_proc', 'rpc'
go
create existing table numbers (i int)
go
select * into #temptable from numbers
go
#temptable contains now the output of the procedure. Instead of #temptable
you could probably also use select * from numbers.
You can read more about this in the 11.5 reference manual or the
Component Integration Services User's Guide to ASE and OmniConnect.
Groetjes,
Luc.
______________________________________________________________________
Luc Van der Veurst ISUG President
Academic Hospital, VUB http://www.isug.com
Laarbeeklaan 101, 1090 Brussels, Belgium
32 - 2 477 69 80
by