使用sp-send-dbmail进行一个又一个的查询

Question

在使用sp-send-dbmail时，我需要在查询后添加正文文本。目前，我发送邮件的存储过程如下所示。

ALTER PROCEDURE [dbo].[sp_SendSFRProcesingEmail]
    -- Add the parameters for the stored procedure here
    (@cmp_code nvarchar(5), @email nvarchar(50), @rbc_email nvarchar(50))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

DECLARE @profile nvarchar(50)
DECLARE @subject nvarchar(100)
DECLARE @querystr nvarchar (MAX)

set @profile = 'Reports'
set @subject  =  'Company Service Fee Processing for ' + @cmp_code
set @querystr = 'SET NOCOUNT ON
SELECT  [Year], [Week], [Description], [Cash_In**], [Cash_Out**], [Amt.Due]
  FROM [001].[dbo].[SFR_Processing_LatestBatch]
  WHERE [cmp_code] = '''+@cmp_code+'''';


EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = 'person@company.com',
@subject = @subject,
@body = 'Note: This is an automatic e-mail message generated by Company.  

The Service Fee Information below was imported via an automated process from RGS. Please compare the information below to your records for the week listed and report any discrepancies immediately.

If you have any questions or concerns regarding this email please contact your Regional Business Consultant.

Thank you!
Company, Inc.
Accounting Department
accounting@company.com

**Amount(s) should equal Item 1  per weekly Service Fee Report for Account Number indicated in Subject Line.

', 
@query = @querystr

END

有没有一种方法可以将查询结果移到身体中间的某个地方，而不是在电子邮件的末尾，如我的示例图所示？

​

​

Answer 1

您可以简单地添加文本作为第二个查询。它将生成上面的连字符行，但是由于上面有NOCOUNT，否则看起来应该很好。

set @querystr = 'SET NOCOUNT ON
SELECT  [Year], [Week], [Description], [Cash_In**], [Cash_Out**], [Amt.Due]
  FROM [001].[dbo].[SFR_Processing_LatestBatch]
  WHERE [cmp_code] = '''+@cmp_code+''';
select ''Thank you!

Company, Inc.
Accounting Department
accounting@company.com

**etc.''';