October 11, 2012 at 5:51 pm
I am using the sp_send_dbmail stored procedure to execute a query and send the results via email in the body of the email message. easy peasy.
The query I have returns a single number and I would like to include that number as part of the subject line to save people from having to open the email to get the metric. I'd like the subject to read:
"Scan Coverage is X"
where X is the result of the query.
How can I make X a variable in the email subject line?
Here is what I have now:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mailrelay',
@recipients = 'myemail',
@subject = 'Scan Coverage is X' ,
@importance = 'High',
@query = 'MY SQL QUERY'
October 12, 2012 at 3:44 am
As it is, you can't. Would it be possible to execute the query, set the result to a variable, and then concatenate that to another variable for use in sp_send_dbmail?
e.g. Execute MY SQL QUERY with result @result
set @subject = 'Scan Coverage is ' + CAST(@result as varchar(10))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mailrelay',
@recipients = 'myemail',
@subject = @subject,
@importance = 'High'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply