How do I select from a stored procedure in Sybase?

Question

My DBA has constructed me a stored procedure in a Sybase database, for which I don't have the definition. If I run it, it returns a resultset with a set of columns and values. I would like to SELECT further to reduce the rows in the result set. Is this possible?

From this question it seems like I could insert the results into a temporary table, but I'm not sure I've got permissions to do this.

Is there any way I can SELECT certain rows, or if not, can someone give me example code for simulating with a temporary table?

Answer 1

In Sybase ASE, we can use this hack to select from a stored procedure via a "proxy table":

http://www.sypron.nl/proctab.html

Example:

sp_addserver loopback, null, @@servername
go

create existing table
sp_test12 (
    Document_Name varchar(100),
    Required_Status varchar(5),
    Doc_ID varchar(10),
    OrderBy int,
    No_of_Copy_Retain int,
    _p_EPEB_ID varchar(10) null,
    _p_MY_NAME varchar(3)  null,
    _p_MY_NO varchar(10)   null,
    _p_EPEB_EDATE datetime null,
    _TXN varchar(10)  null,
    _SUBTXN varchar(15)  null,
    _OwnType_ID1 varchar(5)  null,
    _OwnType_ID2 varchar(5)  null,
    _blnflag int null
)
external procedure
at 'loopback.MYDB.dbo.usp_xyz'
go

select 
Doc_ID, No_of_Copy_Retain, _p_EPEB_ID, _p_EPEB_ID, _p_MY_NAME, _p_MY_NO
from #sp_test12
where
    _p_EPEB_ID='EPEB1508'
    and _p_MY_NAME='107'
    and _p_MY_NO='2011000045'
    and _p_EPEB_EDATE='2011-01-15 15:03:03.0'
    and _TXN='TX012'
    and _SUBTXN='TX012.001'
    and _OwnType_ID1='ASSN'
    and _OwnType_ID2='ASSN'
    and _blnflag=0
go

Answer 2

Under Sybase IQ (12.6 and higher at least) you can select from a stored procedure and filter the results as if it was a table. I do not know if this works under ASE or ASA but you could give it a try.

So if you stored procedure is called myproc an the result set has a column ACTIVE which can be either 0 or 1 and you want to select only the ACTIVE = 1 rows you could do this.

SELECT * FROM myproc() WHERE ACTIVE = 1

Under IQ you can also use this as a derived table and JOIN it with other tables for example like this...

SELECT t1.name,t1.address,t2,active FROM tbl_atable t1, ( SELECT * FROM myproc() WHERE ACTIVE = 1) t2 WHERE t1.active = t2.active

...which is kind of neat!

I hope that works for which ever version of Sybase you are running.