How do I select from a stored procedure in Sybase?

Nick Fortescue picture Nick Fortescue · Sep 18, 2009 · Viewed 26.4k times · Source

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

deepak11 picture deepak11 · Oct 19, 2012

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