CODEWITHSUNDEEP
t-sqlsql-server-2008-r2sp-send-dbmail
d90
d90

Reputation: 787

Text After Query with sp-send-dbmail

I have the need to add body text after the query when using sp-send-dbmail. Currently my Stored procedure to send mail looks like this. 

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 = '[email protected]',
@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
[email protected]

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

', 
@query = @querystr

END

Is there a way to move the query results to the middle of the body somewhere, rather than at the very end of the email as shown in my example pic?

enter image description here

Upvotes: 1

Views: 1362

Answers (1)

BenderBoy
BenderBoy

Reputation: 1301

You can simply add the text as a second query. It will generate the line of hyphens above, but since you have NOCOUNT on it should otherwise look fine.

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
[email protected]

**etc.''';

Upvotes: 1

Related Questions