Using sproc resultset in another sproc

Using sproc resultset in another sproc

Post by Jean-Marc van Leerd » Wed, 14 Oct 1998 04:00:00



Hi all,

This may be a dumb question, but here goes anyway:

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?

Any help is appreciated.

Regards,

Jean-Marc.

   /--- #include: <disclaim.std> ----------------------------/

 /ingbank.com            /                                 /
/----- (there is no xxremovexx in my address) ------------/

 
 
 

Using sproc resultset in another sproc

Post by luc van der veurs » Wed, 14 Oct 1998 04:00:00



: 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      


 
 
 

Using sproc resultset in another sproc

Post by Pieter van Nieuwkoo » Wed, 14 Oct 1998 04:00:00




Quote:

> Hi all,

> This may be a dumb question, but here goes anyway:

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

Sybase has always supported this facility.
You need to use something like this:
create main-proc
as
create #temptable
()
exec sub-proc
...
go
---------------------

create #temptable
()
go
create sub-proc
as
...
insert into #temptable ....
return
go

Care needs to be taken that definition of temp-table is exactly the same in
the 2 stored procedure scripts.

> Any help is appreciated.

> Regards,

> Jean-Marc.

>    /--- #include: <disclaim.std> ----------------------------/

>  /ingbank.com            /                                 /
> /----- (there is no xxremovexx in my address) ------------/

 
 
 

Using sproc resultset in another sproc

Post by Jean-Marc van Leerd » Thu, 15 Oct 1998 04:00:00



Quote:>Sybase has always supported this facility.
>You need to use something like this:
>create main-proc
>as
>create #temptable
>()
>exec sub-proc
>...
>go
>---------------------

>create #temptable
>()
>go
>create sub-proc
>as
>...
>insert into #temptable ....
>return
>go

>Care needs to be taken that definition of temp-table is exactly the same in
>the 2 stored procedure scripts.

Yeah, I know, but as I mentioned in my initial post, we're not really
in a position to change the sub-proc in any way (it's presented to us
by another project for use) :-(

However, since the alternative is to purchase 'Extended Enterprise
Option', we'll have another go at the other project team to try and
persuade them ;-)

Thanks anyway,

Jean-Marc.

   /--- #include: <disclaim.std> ----------------------------/

 /ingbank.com            /                                 /
/----- (there is no xxremovexx in my address) ------------/

 
 
 

1. Execute a SPROC in another SPROC

I am working on SPROC usp_A, inside A uses SPROC usp_B. usp_B takes two
parameters and return one row. The number of columns returned by usp_B is
depend on the parameter value passed to usp_B.

Here is my question, inside usp_A, how can I get the values returned from
usp_B?

NOTE: Rewirte usp_B and use OUTPUT parameters is not an option.

I've thought about the followings:

    But, the problem is I don't know how many columns will be returned in
advance.

2. Use OPENQUERY, OPENROWSET etc.
    Since the SPROCs are in the same server, same database, to use this
functions seems overkill.

Thanks.
Jack Jia

2. IDS 7.13 under different OS ?

3. How to embed CreateTrigger into a sproc or otherwise script it into a sproc

4. Help!

5. Exec sproc from sproc

6. composite indexes?

7. Calling Sproc from Sproc

8. Easy One: What is '0xf' for in 'sysstat & 0xf = 4' //'sysstat & 0xf = 3'?

9. sproc reading tables from another sproc

10. Exec SProc from SProc (output parameter question)

11. Calling Sproc from Sproc and getting only one recordset

12. Sproc as an input parm for another sproc?

13. Calling a sproc from another sproc