output of stored procedure to text and email text

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  September 23, 2021 at 3:52 pm

  #3932259

  i have a stored procedure which outputs data from various table query like below.

  create procedure ki_data

  as

  select * from customer

  select * from order

  select * from invoices

   

  i would like to send the output of stored procedure (in text format) to email .

  I know how i can do it using select statement

  EXEC msdb.dbo.sp_send_dbmail

  @profile_Name ='Administrator',

  @recipients='someemail@company.com',

  @subject = 'Duplicate PO Lot',

  @query = 'SELECT lot, ref_num, ref_line_suf FROM phil_App.dbo.ki_po_dup_lot WHERE prev_lot_used = lot

  ',

  @body = 'Dulicate PO Lot',

  @attach_query_result_as_file = 1 ;

  END

  How do i replace select statement to stored procedure.

   

  • This topic was modified 3 years, 2 months ago by  skb 44459.

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  September 23, 2021 at 4:12 pm

  #3932273

  With the understanding that I've never tried it before, have you tried using EXEC?

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  September 23, 2021 at 5:02 pm

  #3932290

  I would use a cursor for each query in a loop and build up the text you want as an nvarchar(MAX) variable.

  Then just use the text you have built up as part of the body of the email.