possible to link 2 stored procedures to produce only 1 recordset?

Hello everybody!

trying to do the following:

-create a report in access project

-got 3 stored procedures which return data that shall be shown on report

-need one recordset as datasource (or can i use more than one here?)

Problem:

Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.

I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)

Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):

CREATE PROCEDURE IntegrateSPs AS

INTEGRATE
SP1,SP2,SP3
INTO myRecordset

Anything like this possible?

thx in advance,

KumaHow about this:

create a new "wrapper" procedure. that does the something like:

create #temp (with a buch of fields)

insert into #temp exec proc1
insert into #temp exec proc2

select *
from #temp|||How about this:

create a new "wrapper" procedure. that does the something like:

create #temp (with a buch of fields)

insert into #temp exec proc1
insert into #temp exec proc2

select *
from #temp

Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...

Otherwise it won't...

Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)

CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)

AS

SELECT
a + b + c AS fld1,
d + e + f AS fld2

FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd

SP1 would return:
PersonID, Fld1, Fld2, Fld3

SP2:
PersonID, Fld4, Fld5, Fld6

SP3:
PersonID, Fld7, Fld8, Fld9

Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.

SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.

Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.

like this:

Results for PersonID: XYZ123

SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7

If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.

SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?

thx

Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS

CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd

CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd

CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd

SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)

RETURN-PatP|||thx a lot.

hoped for something even simpler, but i can live with that.

Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.

glad i got the syntax for the INSERT statement!

ty again

Kuma|||Just hope the sproc is executed at the same time then...

I would suggest a table variable if you don't like temp tables...

My question to you then, is how BIG is the result set?

Because if it's not then temp is not a problem...

still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.

-PatP|||thx for the info in the last two posts.

table variable sounds very good. i'll try that.

didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...

:)

Kuma

*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!

-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.

-PatP|||rdjabarov: yes pls u should say why :)

ty|||rdjabarov: yes pls u should say why :)

tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.

-PatP