Retreiving stored proc values in another stored proc in Sybase

Posted on 2007-07-22
Last Modified: 2010-07-27
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?
Question by:ashish2k
    LVL 19

    Expert Comment

    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.

    LVL 14

    Accepted Solution

    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).
    LVL 5

    Expert Comment

    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
    LVL 19

    Expert Comment

    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,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Great sound, comfort and fit, excellent build quality, versatility, compatibility. These are just some of the many reasons for choosing a headset from Sennheiser.
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now