SQL Server:修改电子邮件的显示方式

Question

感谢我得到的帮助，我能够修改我的存储过程，以便在满足条件时发送电子邮件。

Name         Start Date
Doe, John    0012-12-16

但是现在我需要再次修改我的存储过程，使电子邮件如下所示。

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

我的存储过程如下所示。

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'[e-mail]',
@subject = 'Incorrect Assignment Dates found',
@body = @tableHTML,
@body_format = 'HTML';

Answer 1

因此，像这样修改存储过程是有效的。

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='[e-mail]',
@subject='Incorrect Assignment Date Found',
@body=@HtmlContent,
@body_format = 'HTML';
END
END