CODEWITHSUNDEEP
sql-serversql-server-2008
Anshul Dubey
Anshul Dubey

Reputation: 378

SQL Server msdb.dbo.sp_send_dbmail

I want to publish some query results in the e-mail in a structured format. I'm using the @body_format as 'HTML'.

I want to do like:

   Declare @processed_rows int
   declare @Sourcetype varchar(100)

   select @sourcetype = (some SQL query) 
   select @processed_rows = (some SQL query) 

   exec msdb.dbo.sp_send_dbmail 
        @body='some HTML' ,
        @subject = 'IGD-DEVELOPMENT SERVER UPDATED',
        @profile_name = 'IGDMail',
        @recipients =  ' [email protected]

When I'm executing msdb.dbo.sp_send_dbmail, I want to pass these variables in the @body argument of stored procedure and use them in the final email, please help to achieve this?

Upvotes: 1

Views: 193

Answers (1)

John Bell
John Bell

Reputation: 2350

You'll need to declare another variable to hold your HTML code:

DECLARE @HTML NVARCHAR(MAX)

SET @HTML = 'SOME HTML HERE' + CAST(PROCESSED_ROWS AS VARCHAR) + 'SOME MORE HTML' + @SOURCETYPE

EXEC MSDB.DBO.SP_SEND_DBMAIL 
@BODY = @HTML
, @BODY_FORMAT = 'HTML'
ETC.....

You get the idea...

Upvotes: 1

Related Questions