How do I insert the result set of a stored procedure into a temp table AND get the output parameters in Sybase?

Question

I'm currently on Sybase ASE 15.7 and writing a stored procedure that uses the result of another SP. I would like to call it and insert the result into a temp table so no modification is needed for the original SP.

Refering to this post: How can I get data from a stored procedure into a temp table?

Jakub's answer using proxy tables works perfectly with the sample SP definition:

create procedure mydb.mylogin.sp_extractSomething (
@timestamp datetime) as
select column_a, column_b
    from sometable
    where timestamp = @timestamp

However there is one last piece missing! How do you get the output parameters AND the result set? The SP in my case is defined like the following:

create procedure mydb.mylogin.sp_extractSomething 
(
    @timestamp datetime, 
    @errcode    char(10) output
) as
    select @errcode='NOERR'
    select column_a, column_b
        from sometable
        where timestamp = @timestamp    
    if (@@rowcount = 0)
    begin
        select @errcode = 'ERR001'
    end

I'd defined and used the proxy tables as following:

--create proxy table
create existing table myproxy_extractSomething (
column_a int not null, 
column_b varchar(20) not null,
_timestamp datetime null,
_errcode char(10) null) external procedure at "loopback.mydb.mylogin.sp_extractSomething"

--calling sp
declare @errcode Char
declare @myTimestamp datetime
set @myTimestamp = getdate()

select * 
from myproxy_extractSomething
where _timestamp = @myTimestamp
and _errcode = @errcode
select @errcode

While the result set can be returned successfully, @errcode / _errcode is always null. How can I define a output parameter in a proxy table?

score 0 · Accepted Answer · answered May 16 '16 at 09:29

It truns out even creating proxy tables are not allowed in stored procedures. The following error will pop:

Statement with location clause must be the only statement in a query batch

So I guess in Sybase sp there is no ways to use the result data set of another sp without modifying the original one. Unfortunately, it seems like the only way left is copying the orignal sp (which has 1.4k lines).

How do I insert the result set of a stored procedure into a temp table AND get the output parameters in Sybase?

1 Answers1