SQL Adding three separate tables to an email

Question

I'm trying to send out an email with two seprate tables.
How do I do that?

Adding sample data, per someone's request.

This is what the email should look like

Items

Item  Price
Apples 1.25
Oranges 2.24
Banana 0.29

Sales

Month  Item  Sold
Feb  Apples $5.00
Feb  Oranges $10.00

Here is the code. I had to comment out the if statement and delete a begin for it to compile without errors.

    Declare @nvMessage nvarchar(1000)
       Declare @nvSubject nvarchar(1000)
       Declare @nvQuery nvarchar(4000)
        DECLARE @tabdata nvarchar(max)
        DECLARE @table nvarchar(max)
        DECLARE @tableHTML nvarchar(max)
       Declare @iWidth int
       Set @iWidth = 4000
       Set @nvSubject = 'Month End Report'

        Declare @MonthBegin datetime
        Declare @MonthEnd datetime
        Set @MonthBegin = DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)
        Set @MonthEnd = DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))

------------------------------------------------------------------------------------------------------------------------


 SET @tableHTML =  
                N'<h2><font face="Calibri">Report: ' + '</font></h2>' + 
                N'<table border="1" rules="none" cellpadding="6" cellspacing="-1"><font face="Calibri" size=2>' + 
                N'<tr>' +
                N'<th bgcolor="#C5D9F1">Table Field you want to display</th>' +
                N'<th bgcolor="#C5D9F1"> Another Table Field you want to display</th>' +
                N'</tr>' + 
                CAST
                (
                (
                    SELECT * FROM ITEMS     
                    FOR XML PATH('tr'), TYPE  
            ) AS NVARCHAR(MAX)
            ) +'</table>' + '<hr color = "black"> </hr>'


--IF
--  (
--    SELECT
--        COUNT(*)
--    FROM 
--        table2 
--   ) > 0

-- if there is data in the second table then show it. If not dont. 

   SET @tableHTML = @tableHTML +
                N'<h2><font face="Calibri">Report: ' + '</font></h2>' + 
                N'<table border="1" rules="none" cellpadding="6" cellspacing="-1"><font face="Calibri" size=2>' + 
                N'<tr>' +
                N'<th bgcolor="#C5D9F1">Table Field you want to display</th>' +
                N'<th bgcolor="#C5D9F1"> Another Table Field you want to display</th>' +
                N'</tr>' + 
                CAST
                (
                (
            SELECT * FROM SALES

                    FOR XML PATH('tr'), TYPE  
            ) AS NVARCHAR(MAX)
            ) +'</table>' + '<hr color = "black"> </hr>'


-- then send out your mail 

BEGIN  

              SET @nvMessage = 'Month End  Report'
              EXEC msdb.dbo.sp_send_dbmail_mod
                     @recipients = @nvRecipients,
                     @subject = @nvSubject,
                     @body = @tableHTML,
                     @body_format = 'HTML'
END

Now I have too much code and it wants me to add more text. Now I have too much code and it wants me to add more text. Now I have too much code and it wants me to add more text. Now I have too much code and it wants me to add more text.

Answer 1

I usually use an HTML table to do this. This will create an email with multiple tables.

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10);

If
  (
   SELECT 
        COUNT(*)
   FROM 
        #TABLE_YOU_WANT_DATA_FROM 
  ) = 0 

BEGIN
    SET @tableHTML = @NewLineChar
END 
    SET @tableHTML =  
                    N'<h2><font face="Calibri">Report: ' + '</font></h2>' + 
                    N'<table border="1" rules="none" cellpadding="6" cellspacing="-1"><font face="Calibri" size=2>' + 
                    N'<tr>' +
                    N'<th bgcolor="#C5D9F1">field1</th>' +
                    N'<th bgcolor="#C5D9F1">field2</th>' +
                    N'</tr>' + 
                    CAST
                    (
                    (
                        SELECT

                            td = x1,'' AS [field1],
                            td = x2 ,'' AS [field2]
                        FROM
                            TABLE_YOU_WANT_DATA_FROM             
                        FOR XML PATH('tr'), TYPE  
                ) AS NVARCHAR(MAX)
                ) +'</table>' + '<hr color = "black"> </hr>'
    END

    IF
      (
        SELECT
            COUNT(*)
        FROM 
            table2 
       ) > 0

    -- if there is data in the second table then show it. If not dont. 
    BEGIN
       SET @tableHTML = @tableHTML +
                    N'<h2><font face="Calibri">Report: ' + '</font></h2>' + 
                    N'<table border="1" rules="none" cellpadding="6" cellspacing="-1"><font face="Calibri" size=2>' + 
                    N'<tr>' +
                    N'<th bgcolor="#C5D9F1">field1</th>' +
                    N'<th bgcolor="#C5D9F1">field2</th>' +
                    N'</tr>' + 
                    CAST
                    (
                    (
                        SELECT

                            td = x1,'' AS [field1],
                            td = x2 ,'' AS [field2]
                        FROM
                            TABLE_YOU_WANT_DATA_FROM             
                        FOR XML PATH('tr'), TYPE  
                ) AS NVARCHAR(MAX)
                ) +'</table>' + '<hr color = "black"> </hr>'
    END

    -- then send out your mail 

    BEGIN  

                  SET @nvMessage = 'Month End  Report'
                  EXEC msdb.dbo.sp_send_dbmail_mod
                         @recipients = @nvRecipients,
                         @subject = @nvSubject,
                         @body = @tableHTML,
                         @body_format = 'HTML'
    END

Answer 2

Poor solution, but this is as close as you can get using send_dbmail. It only works if you have the same columns in both tables:

SET @nvquery = 'SELECT * FROM #TME1 UNION ALL SELECT * FROM #TME1 WHERE 1=2 UNION ALL SELECT * FROM #TME2'