output of stored procedure to text and email text

  • 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 1 month ago by  skb 44459.
  • 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)
    Intro to Tally Tables and Functions

  • 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.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply