Retreiving stored proc values in another stored proc in Sybase

I have a stored procedure in sybase and I want to run it with two different parameters in a new stored
procedure and return the total of both the stored procs in this new stored procedure. e.g. I have a stored proc sp1 that i have to run with 2 different parameters i.e run first time sp1 with parameter A=1 which will return 20 records and then I will run sp1 with parameter A=2 which will return 30 records, now I want to store these values somewhere and return 50 records in my new stored proc.
Can somebody please help?
Who is Participating?
Jan FranekCommented:
Other possible approach is storing results of stored procedure into temporary tables, but you have to be able to modify existing procedure.

1. You have to modify existing procedure so it doesn't return results with select statement but store it into prepared temporary table using insert into statement. Temporary table has to exists before you create your procedure and you have to drop it after creating procedure. So the script will look like this:

create table #temp ( ... )
create procedure X ...
drop table #temp

2. New procedure has to create temporary table with the same name and structure as the one used when modifying your original procedure, then call "subprocedure" twice and then select from temporary table:

create procedure Y
create table #temp ( ... )
exec X param1
exec X param2
select * from #temp

I use this technique quite often and sometimes I need to keep original procedure returning select. In theese cases I usually create new subprocedure storing results in temporary table and then use this new subprocedure in both original procedure (calling it once) and new procedure (calling it twice).
Yes, you can do this but it takes a couple of tricks to make it happen.

First, since you are going to have to use CIS to do this, you need to setup a remote server, using sp_addserver, that points to itself.  Add an entry (I usually call it loopback) to the interfaces file that points to the server you are working with using DSEDIT.  Then add the "remote" server with sp_addserver, again calling it "loopback".

Next, create a proxy table against the stored procedure you wish to call.  This is done with the CREATE EXISTING TABLE command and is documented in the "Reference Manual: Commands".  The target of the proxy table is actually the stored procedure and the column definition is that of the result set plus one or more special argument columns.  These are named after the arguments to the stored procedure with the "@" replaced with an underscore.

Finally, to retrieve results from the stored procedure/proxy table, you do a select and pass the argument in the WHERE clause:

SELECT * FROM myproxytab WHERE _A = 1

Now, you have one more complication.  You need to call the stored procedure twice and combine the results.  You have a couple of choices here.  You can do a SELECT/INTO for the first query/call and and INSERT/SELECT for the second, or you can do a UNION ALL.

SELECT * FROM myproxytab WHERE _A = 1
SELECT * FROM myproxytab WHERE _A = 2

That should do the trick for you plus you now have a very powerful tool to add to your arsenal.

Using CIS might be overkill.
Why can't you create a temp table ahead of calling the proc and then have it populate that table by calling in twice; once for each different parameter? The temp table would then have the combined result-set of both procedure calls
Jan and hkamal make a good point.

If you can rewrite or create a new stored procedure, it is probably easier and certainly more straight forward to create the temp table then have the new procedure (that takes multiple arguments) populate that temp table with Insert/Select(s).

I assumed, perhaps incorrectly, that the stored procedure you were calling is invariant for some reason and/or it is undesirable to duplicate the logic.

Best of luck,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.