How to format data purge email report

Question

I need to send an emailed monthly report which has the results of a data purge and in that report I need to have the 2 columns below.

1. Table name
2. Number of records deleted

Emailed report needs to look like:

TableName | Deleted Records
---------------------------
dbo.table1  | 4287
dbo.table2  | 90276
dbo.table3  | 382

Below is a an example of my delete statements. I have put them in a stored proc.

      Delete from dbo.table1 where date = 'value'
      SELECT 'dbo.table1' as Tablename , @@ROWCOUNT AS 'Record_Count'
      Delete from dbo.table2 where date = 'value'
      SELECT 'dbo.table2' as Tablename , @@ROWCOUNT AS 'Record_Count'
      Delete from dbo.table3 where date = 'value'
      SELECT 'dbo.table3' as Tablename , @@ROWCOUNT AS 'Record_Count'

When I run the following to send the report, the emailed format isn't clean.

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'SQL DBA',  
      @recipients = 'myemail.com',  
      @body = 'Please see the report below for this months Data Purge',
      @body_format = TEXT,
      @query = 'EXECUTE datapurge SP',
      @execute_query_database = 'databasenamegoeshere',
      @subject = 'Data purge record count',
      @query_result_header = 1,
      @query_result_width = 256,
      @query_result_separator = ',',
      @query_result_no_padding = 1;

    Actual emailed results:

dbo.table1,10641
Tablename,Record_Count
---------,------------
dbo.table2,112141
Tablename,Record_Count




But it needs to look like:

TableName   | Deleted Records
---------------------------
dbo.table1  | 10641
dbo.table2  | 112141
dbo.table3  | 382

or

TableName   | Deleted Records
----------------------------
dbo.table1  | 10641
----------------------------
dbo.table2  | 112141
----------------------------
dbo.table3  | 382

Answer 1

Answering here instead. You can change your procedure to something like:

create procedure datapurge
as
begin
      create table #results (tablename sysname, cnt INT)

      Delete from dbo.table1 where date = 'value'
      insert into #results
      SELECT 'dbo.table1' as Tablename , @@ROWCOUNT AS 'Record_Count'
      Delete from dbo.table2 where date = 'value'
      insert into #results
      SELECT 'dbo.table2' as Tablename , @@ROWCOUNT AS 'Record_Count'
      Delete from dbo.table3 where date = 'value'
      insert into #results
      SELECT 'dbo.table3' as Tablename , @@ROWCOUNT AS 'Record_Count'
      select *
      FROM #results
      ORDER BY 1
end

This way, the output will be a bit nicer