Possible to send the result from store procedure to temp table

Possible to send the result from store procedure to temp table

Post by Francis Le » Wed, 15 May 2002 22:54:10



Hi Folks,

Could I send the result of stored procedures to temp. table in Sybase 12?

Regards,
  Francis

 
 
 

Possible to send the result from store procedure to temp table

Post by admi » Wed, 15 May 2002 23:26:07



Quote:

> Could I send the result of stored procedures to temp. table in Sybase 12?

Sure.  You can declare the temp table outside the create procedure statement
in your script when you compile.  Just be sure to declare the temp table again
at runtime from the calling process.

 
 
 

Possible to send the result from store procedure to temp table

Post by Larry Coo » Thu, 16 May 2002 00:27:13



> Could I send the result of stored procedures to temp. table in Sybase 12?

Yes.  The problem that you're probably running into is that
when the stored procedure exits, any temp tables created within
the procedure are automatically dropped.  So you create the
temp table outside the procedure, and have the procedure insert
into the (already created) temp table.  Like this:

create procedure outer as
  create table #temp (a int, b int)
  exec inner
  select * from #temp
return

create procedure inner as
  insert into #temp(a,b) select 1, 2
return

But there's a gotcha here: inner won't compile because #temp
doesn't exist.  So you have to create #temp temporarily in
order to create inner:

create table #temp (a int, b int)

create procedure inner as
  insert into #temp(a,b) select 1, 2
return

drop table #temp

Larry Coon
University of California


 
 
 

Possible to send the result from store procedure to temp table

Post by Francis Le » Fri, 17 May 2002 02:31:55


Hi  Larry,

Thanks for your reply,  but the problem is that there is that I would like
to capture the result of the store procedure (it is maintained by others and
I cannot modify it), how could I capture the result of it?

For example, when you execute sp_who, could I capture the result to a
temporary table for further manipluation?  Like function call, I do not need
to update the call directly for retrieving the value.

Thanks and Regards,
  Francis



> > Could I send the result of stored procedures to temp. table in Sybase
12?

> Yes.  The problem that you're probably running into is that
> when the stored procedure exits, any temp tables created within
> the procedure are automatically dropped.  So you create the
> temp table outside the procedure, and have the procedure insert
> into the (already created) temp table.  Like this:

> create procedure outer as
>   create table #temp (a int, b int)
>   exec inner
>   select * from #temp
> return

> create procedure inner as
>   insert into #temp(a,b) select 1, 2
> return

> But there's a gotcha here: inner won't compile because #temp
> doesn't exist.  So you have to create #temp temporarily in
> order to create inner:

> create table #temp (a int, b int)

> create procedure inner as
>   insert into #temp(a,b) select 1, 2
> return

> drop table #temp

> Larry Coon
> University of California



 
 
 

Possible to send the result from store procedure to temp table

Post by Larry Coo » Fri, 17 May 2002 03:31:19



> Hi  Larry,

> Thanks for your reply,  but the problem is that there is that I would like
> to capture the result of the store procedure (it is maintained by others and
> I cannot modify it), how could I capture the result of it?

> For example, when you execute sp_who, could I capture the result to a
> temporary table for further manipluation?  Like function call, I do not need
> to update the call directly for retrieving the value.

You can't do that directly.  What you might want to look
into doing is using Component Integration Services to set
up a proxy table that is an RPC call.  That _might_ give
you what you're looking for.

Larry Coon
University of California


 
 
 

Possible to send the result from store procedure to temp table

Post by who » Sat, 18 May 2002 14:35:14


You do not have to use CIS or RPCs to perform this.  I am assuming
that you have a stored proc that cannot be modifed but you want to
take the data returned from the proc and manipulate the data before it
leaves the server.

If this is the case then it depends what the stored proc does.  You
might even be better off performing your own statement ...  i would
know what the proc does and what you want to do with it ....  code is
always the best (sp_helptext proc_name) ....

The best way, off-hand, is either cascaded/nester procs or your own
statement.

Hope this helps



> > Hi  Larry,

> > Thanks for your reply,  but the problem is that there is that I would like
> > to capture the result of the store procedure (it is maintained by others and
> > I cannot modify it), how could I capture the result of it?

> > For example, when you execute sp_who, could I capture the result to a
> > temporary table for further manipluation?  Like function call, I do not need
> > to update the call directly for retrieving the value.

> You can't do that directly.  What you might want to look
> into doing is using Component Integration Services to set
> up a proxy table that is an RPC call.  That _might_ give
> you what you're looking for.

> Larry Coon
> University of California