CODEWITHSUNDEEP
t-sqlstored-proceduresselectsybase
Nick Fortescue
Nick Fortescue

Reputation: 44183

How do I select from a stored procedure in Sybase?

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?

Upvotes: 6

Views: 30855

Answers (7)

deepak11
deepak11

Reputation: 61

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

Upvotes: 4

Ian Kemp - SE killed by LLMs
Ian Kemp - SE killed by LLMs

Reputation: 29899

As far as I know, this is not possible in Sybase ASE. Even using

insert #temp_table
exec my_procedure

doesn't work (at least on sybase 12.x).

Upvotes: 2

user1746917
user1746917

Reputation: 11

In Sybase IQ, you can do this:

select < col1>, < col2> from < sp_name>('< sp_arg>') where < predicate>

Example:

select Object, DbspaceName, ObjSize from sp_iqindexinfo ('table xyz') where Object like '%col1_indx%'

Upvotes: 1

B0rG
B0rG

Reputation: 1225

Just a thought.

Perhaps your DBA could prepare a view instead of a stored procedure, if he wanted you for some reason not to look at the inner stuff or worry about it.

Another approach would be to see the stored procedure text (unless encrypted) with sp_helptext and rewrite it for your own purposes (eg. into a view) to be able to apply additional conditioning to the resultset.

Upvotes: 1

Paul Harrington
Paul Harrington

Reputation: 782

It is possible with ASE but in a rather roundabout kind of way using CIS and proxy tables. The mechanism is described very well on Rob Verschoor's site:

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

I tried it out once as a curiosity and indeed it does work. I did not delve into the tricky question of error-handling.

pjjH

Upvotes: 2

Kevin Horgan
Kevin Horgan

Reputation: 1580

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.

Upvotes: 3

mmmmmm
mmmmmm

Reputation: 32700

You will need to ask the DBA to change the stored procedure.

You could get it changed to select the results into a temporary table rater than a plain select and then you can write your own select on that temp table to return only the rows you want

Upvotes: 2

Related Questions