Combining Multiple Result Sets From Stores Procedure

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  July 11, 2011 at 6:27 am

  #239417

  Dear All,

  My Boss has assigned me a task to send me result of a procedure through Database mail. The procedure itself contain call to some procedures.

  I have Configured Database mail successfully. I want to know, how Can I Combine multiple result sets from a SP into a temp table or Variable, so that I cant sent it through DB Mail.

  The code is given below

  I would be very thankful to you for help.

  ALTER PROCEDURE [dbo].[CR__M_0_RectifyInconsistency]

  @pRectify BIT=0

  AS

  BEGIN

  exec [CR__M_10_CheckInconsistentLoanees] @pRectify

  exec [CR__M_11_CheckMissingSchedulesAndRecovery] @pRectify

  exec CR__M_12_CheckLedgerAndRecoveryReceipts @pRectify

  exec CR__M_13_RectifyMissingLegerFromRecoveryReceipts @pRectify

  EXEC CR__M_2_RectifyBehboodFundAndRecoveryReceipts @pRectify

  exec CR__M_31_CheckLedgerAndWriteOffReceipts @pRectify

  exec CR__M_32_RectifyMissingLegerFromWriteOffReceipts @pRectify

  SELECT 'Wrong Phase'

  EXEC CR__M_6_RectifyLoaneePhase @pRectify

  RETURN

  END

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  July 11, 2011 at 8:42 am

  #1351118

  Presumably all of these other procedures are returning results you want? Just create a temp table and insert you exec statements.

  create table #blah

  (

  ...columns...

  )

  insert #blah

  exec MyProc

  insert #blah

  exec MyOtherProc

  That make sense?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/