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

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

insert #temp_table
exec my_procedure

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

Answer 2

Have a look at my answer here. This relies on you being able to create a temp table though.

Answer 3

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

Answer 4

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.

Answer 5

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