How can I set sp_send_dbmail to email the query results?

Question

I've been tasked with creating a list of departed users that we need to archive in an in-house app.

I've written the select statement (SQL Server 2012) and it works fine. I can get the email to send from EXEC sp_send_dbmail but the email only has the line I put in the body. When I change the @body info, it still just uses the original line I entered. It's not changing for what I've added there, and I can't get it to send the results of the Select.

Here what I have so far:

Set @Results = 'SELECT  p.UID
            ,p.EMPNO
            ,p.FULLNAME AS FULL_NAME
            ,p.Archived
            ,p.COMPANY
            ,p.EMAIL
            ,p.SID
            ,e.TERMCODE AS TERM_CODE
            ,CONVERT(VARCHAR, e.FIREDATE, 101) AS FIRE_DATE
        FROM app_table p
        LEFT OUTER JOIN employee_table e ON e.EMPNO=p.EMPNO
        WHERE p.EMPNO IS NOT NULL
            AND p.ARCHIVED = 0
            AND e.FIREDATE IS NOT NULL
        ORDER BY e.FIREDATE DESC'

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SQL_Mailer',
        @recipients = '[email protected]', 
        @subject = 'Departed Users being archived',
        @body = @Results
END

I've also tried adding @query to the sp_send_dbmail and putting the Select into the @query: @query = 'Select statement above'

What am I missing?

Answer 1

You must pass the query in @query parameter and specify @attach_query_result_as_file=1. @body is just a text to be included in the message. See sp_send_dbmail for more details. You said you tried that, but didn't said what happened and your code doesn't do that.

Set @Results = 'SELECT  p.UID
        ,p.EMPNO
        ,p.FULLNAME AS FULL_NAME
        ,p.Archived
        ,p.COMPANY
        ,p.EMAIL
        ,p.SID
        ,e.TERMCODE AS TERM_CODE
        ,CONVERT(VARCHAR, e.FIREDATE, 101) AS FIRE_DATE
    FROM app_table p
    LEFT OUTER JOIN employee_table e ON e.EMPNO=p.EMPNO
    WHERE p.EMPNO IS NOT NULL
        AND p.ARCHIVED = 0
        AND e.FIREDATE IS NOT NULL
    ORDER BY e.FIREDATE DESC'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL_Mailer',
    @recipients = '[email protected]', 
    @subject = 'Departed Users being archived',
    @body = 'You can write something here',
    @query = @Results,
    @attach_query_result_as_file = 1