SQL Server: modifying how the email message is displayed

Question

Thanks to the help I received, I was able to modify my stored procedure to send the email below when a condition is met.

Name         Start Date
Doe, John    0012-12-16

But now I need to modify my stored procedure again to make the email look like below.

Name: Doe, John
Start Date: 0012-12-16

My stored procedure looks like this.

DECLARE @tempTable NVARCHAR(MAX)

BEGIN

    SET @tempTable = 
        N'<table border = "0">' + 
        N'<tr><th>Name</th>' + 
        N'<th>Start Date</th></tr>' + 
        CAST ( (Select td = r.rscmaster_name_ch,   '',
                       td = a.assign_from_da
                FROM [dbo].[Assign_Tbl] a
                left join [dbo].[Resource_Master_Tbl] r on r.RscMaster_No_In = a.RscMaster_No_In
                where  
                (
                (a.Assign_From_Da like '0%' or a.Assign_From_Da like '00%' or a.Assign_From_Da like '000%')  
                and (a.Assign_Thru_Da like '0%' or a.Assign_Thru_Da like '00%' or a.Assign_Thru_Da like '000%')
                )
                order by r.RscMaster_Name_Ch 
                FOR XML PATH('tr'), TYPE
            ) as NVARCHAR(MAX) ) +
            N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail
    @recipients=N'[email protected]',
    @subject = 'Incorrect Assignment Dates found',
    @body = @tableHTML,
    @body_format = 'HTML';

Answer 1

So, modifying the stored procedure like this worked.

Declare 

@Name VARCHAR(MAX),
@StartDate DATE,
@htmlContent NVARCHAR(MAX)

SELECT r.rscmaster_name_ch, a.Assign_From_Da
INTO #Data1
FROM [Assign_Tbl] a
left join [Resource_Master_Tbl] r on r.RscMaster_No_In = a.RscMaster_No_In
where 
( 
(a.Assign_From_Da like '0%' or a.Assign_From_Da like '00%' or a.Assign_From_Da like '000%')
)

IF (SELECT COUNT(*) FROM #Data1) > 0 BEGIN

SET @HtmlContent = '
    <table>
        <tr>
        </tr>'

DECLARE DataCursor CURSOR FOR SELECT * FROM #Data1
OPEN DataCursor

FETCH NEXT FROM DataCursor INTO @Name, @StartDate

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @HtmlContent = @HtmlContent + '
        <tr>
            <td> Name: ' + @Name + '</td>
        </tr>
        <tr>
            <td> Assignment StartDate: ' + FORMAT(@StartDate, 'MM/dd/yyyy') + '</td>
        </tr>'

    FETCH NEXT FROM DataCursor INTO @Name, @StartDate
END

CLOSE DataCursor
DEALLOCATE DataCursor

SET @HtmlContent = @HtmlContent + '</table>'

EXEC msdb..sp_send_dbmail
    @recipients='[email protected]',
    @subject='Incorrect Assignment Date Found',
    @body=@HtmlContent,
    @body_format = 'HTML';

END

END