[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Retreiving stored proc values in another stored proc in Sybase

Posted on 2007-07-22
4
Medium Priority
?
1,504 Views
Last Modified: 2010-07-27
Hi,
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?
Thanks.
Ashish
0
Comment
Question by:ashish2k
  • 2
4 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 19543082
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
UNION ALL
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.

Regards,
Bill
0
 
LVL 14

Accepted Solution

by:
Jan Franek earned 375 total points
ID: 19545680
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 ( ... )
go
create procedure X ...
go
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
as
create table #temp ( ... )
exec X param1
exec X param2
select * from #temp
go

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

Expert Comment

by:hkamal
ID: 19546180
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
0
 
LVL 19

Expert Comment

by:grant300
ID: 19550529
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,
Bill
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
LinkedIn blogging is great for networking, building up an audience, and expanding your influence as well. However, if you want to achieve these results, you need to work really hard to make your post worth liking and sharing. Here are 4 tips that ca…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

830 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