Calling Stored proc from within a stored proc

• David Howard

  Valued Member

  Points: 62

  More actions

  August 12, 2004 at 6:57 am

  #108579

  I am not sure how to best word this, but basically I would like to aggregate results from several stored procs into one call to the DB, and return one recordset.  Each SP returns the same data set columns, but with different data.  There is always only ONE record returned from each.  They are all written, but the developer has requested the ability to make one call instead of a dozen or so.  Here is a breakdown of what I have:

  SPs to call:

  usp_GetDataSet1

  usp_GetDataSet2

  usp_GetDataSet3

  usp_GetDataSetn

  I would like an sp that is called (for example):

  usp_GetDataSetALL

  The last would inside of it call all of the other SPs, and take the results (each one record) and combine them into one recordset with 12 rows (if there were 12 SPs called for example).

  It would be great if I could just:

  Insert Into #BigRecordSet Select from usp_GetDataSet1

  Insert Into #BigRecordSet Select from usp_GetDataSet2

  etc.

  But that obvioulsy does not work .  I did see where If i rewrote the individual USPs as functions I could probably do that, but for various other reasons I prefer to keep the regular stored procs available to the UI.

  Any help appreciated!

  David

   

   

   

• Dølle Døck

  Mr or Mrs. 500

  Points: 577

  More actions

  August 12, 2004 at 8:22 am

  #518684

  Try this:

  Insert #BigRecordSet

  Exec usp_GetDataSet1

  etc...

  ---

  Regards,

  Anders Dæmroen
  epsilon.no