Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Unanswered: 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,

    Kuma

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How about this:

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

    Code:
    create #temp (with a buch of fields)
    
    insert into #temp exec proc1
    insert into #temp exec proc2
    
    select *
    from #temp

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    How about this:

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

    Code:
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2004
    Posts
    15
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could use something like:
    Code:
    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

  6. #6
    Join Date
    Sep 2004
    Posts
    15
    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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  9. #9
    Join Date
    Sep 2004
    Posts
    15
    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

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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? ).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Temp tables work nicely though, and have every benefit needed for this problem.

    -PatP

  13. #13
    Join Date
    Sep 2004
    Posts
    15
    rdjabarov: yes pls u should say why

    ty

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Kuma
    rdjabarov: yes pls u should say why

    ty
    The source for your INSERT is EXECUTE
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •