adding dynamic content within body of an email (database mail) with sp_send_dbmail

Question

Is there a way I can add the time in the following query as the body of my email :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'TEST_DEV',
    @recipients = 'xxx@gmail.com',
    @query = ' select 
            Percentage = CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.Exception  != ' ' then PD.Id  END) as float)/CAST(COUNT(PD.Id) as float)*100))
         from 
                DataBaseName.dbo.Product P INNER JOIN DataBaseName.dbo.LogProduct PD 
                ON P.LogId = PD.LogId

                WHERE   
                ResponseTime < GETDATE() and  RequestTime > DATEADD(MINUTE, -150, GETDATE())
                ' ,
    @subject = 'Test',
@body = 'Please check the attached file for Providers with Many unsuccessful calls between the time xx an yy',
    @attach_query_result_as_file = 1 ;

In the current line

@body = 'Please check the attached file for info on calls between the time xx an yy',

I would like to add GetDate() in place of xx and DATEADD(MINUTE, -150, GETDATE()) in place of yy ?

is it possible ?

declare @body nvarchar(max) 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DEV',
    @recipients = 'xxx@gmail.com',
    @query = 'exec Database.dbo.spTest' ,
    @subject = 'Test',
 select @body = 'Please check the attached file for info on calls between the time ........................',
    @attach_query_result_as_file = 1 ;

Would you want me to do something like this ?

tommy_o · Answer 1 · 2013-08-20T21:28:34.843

You can declare your @body variable before the EXEC statement and make it any string that you'd like.

Edit:

I updated this to be more verbose. I dont have sp_send_dbmail configed anywhere to test, but I think it should work fine. I created a string variable called @bodyMsg, set it to the string you want before the stored procedure call, then gave the value over to the @body variable in sp_send_dbmail.

declare @bodyMsg nvarchar(max) 

select @bodyMsg = 'Please check the attached file for info on calls between the time ' + convert(varchar,GETDATE()) + ' and ' + convert(varchar,DATEADD(mm, -150, getdate())) + '.'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'TEST_DEV',
    @recipients = 'xxx@gmail.com',
    @query = ' select 
            Percentage = CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.Exception  != ' ' then PD.Id  END) as float)/CAST(COUNT(PD.Id) as float)*100))
         from 
                DataBaseName.dbo.Product P INNER JOIN DataBaseName.dbo.LogProduct PD 
                ON P.LogId = PD.LogId

                WHERE   
                ResponseTime < GETDATE() and  RequestTime > DATEADD(MINUTE, -150, GETDATE())
                ' ,
    @subject = 'Test',
    @body = @bodyMsg,
    @attach_query_result_as_file = 1 ;

Then just pass the @body variable to the sp_send_dbmail stored procedure. Different datetime formats can be found here: http://technet.microsoft.com/en-us/library/ms187928.aspx

The type of `@body` argument should be `NVARCHAR(something)`. [link](http://technet.microsoft.com/en-us/library/ms190307.aspx) — Bogdan Sahlean, Aug 20 '13 at 17:12
Thanks, corrected. I was looking at that same page and didnt see the `n` before `varchar` O.o — tommy_o, Aug 20 '13 at 17:16
Hey tommy_0 , I updated my answer above ? Is that what you meant ? It doesnt work , but I am not sure if I correctly understood your point.What do you mean by pass the @body variable to the sp_send_dbmail. Can you look at my edited answer and let me know ? — CodeNinja, Aug 20 '13 at 18:21

adding dynamic content within body of an email (database mail) with sp_send_dbmail

1 Answers1