Possible to use result from stored Procedures in new query?

[stable]

Hi there,

I'm new to adp and SQL databases.

I've created 3 stored procedures that all have 2 input parameters that I will pass across from vba code. I want to use the results from these stored procedures in a "query" where I can use RIGHT JOIN from one result to the other two.

In an mdb database I would have had 3 queries as input to a new query.

Could anyone advise me as to how I go about this in an adp? Is it somehow possible to use the results of the 3 stored procedure in an stored procedure that will output the required results? Do I need to store the result from the stored procedures into temp tables? I hope not.

Cheers
Stable

 

[cmmrfrds]

You cannot join the results from stored procedures, but you could with views. You could make a view and then join that view to a table or another view much like you did with the mdb queries.

 

[stable]

Hi cmmrfrds,

Thanks for you reply.

I omitted to mention that I wanted to use the results for a report.

I now have a main report with 2 subsreports - all with stored procedures for the recordsource.

In the main report I have the master record details and its latest transaction record. The first subreport contains details of the transaction prior to the latest. The second subreport contains the details of the most recent transaction record satisfying a certain criteria. This seems to work OK.

I'm just wondering whether this is the most efficient method or whether there is a smarter way of reporting these details.

Cheers
Stable

 

[cmmrfrds]

For what you describe, what you are doing is fine. Maybe if the app was doing thousands of transactions a second you could teak it a little. Is this NOT fast?

 

[stable]

Hi cmmrfrds,

I'm using a database containing 3000 master records with 9000 transaction records and it seems to work quite fast.

Thanks for the reassurance.

Cheers
Stable