dbo.sp_send_dbmail and HTML when one email SPROC calls existing SPROC

Question

I want to build HTML and use sp_send_dbmail to send it. Option C on this page explain how to do that. http://msdn.microsoft.com/en-us/library/ms190307.aspx

But here's my catch. I have created a stored proc called AbcStats. Quite often, I go to SSMS and just do "exec ABCStats" with no email, and just want to see the result set in the datagrid on the screen. I have set some of my own criteria that rates the results as "Good" or "Bad", and that stored proc returns 1 when anything is Bad and 0 when all is Good.

I then created a SQL agent job that runs Sproc JobAbcAlert as shown below:

ALTER Procedure 
[dbo].[JobAbcAlert] 
(
   @SendEmailOnlyOnError bit = 1 
   )
as
begin


DECLARE @Status int 
DECLARE @BoolSendEmail bit = 0 
DECLARE @emailSubject varchar(100) = 'BizTalk Feed Status - Good' 

-- Call Stored Proc first time to find out if status is "GOOD" or "BAD"
-- which is used to determine if email is sent, and what is subject of email 

EXEC @status = AbcStats @report='short'  -- faster to run with "short" report option 

if @status = 1  -- (0=all good, 1=at least one "BAD" encountered)  
   Begin
      print 'BAD status, setting email subject' 
      SET @emailSubject = 'QT PRODUCTION ISSUE: BizTalk Feed Status - CRITICAL - contact BizTalk team ' 
   End 

if @status = 1 or @SendEmailOnlyOnError = 0  
   Begin
      SET @BoolSendEmail = 1 
   End 

if @BoolSendEmail = 1 
   Begin
        print 'sending email' 
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'DoNotReply@SomeCompany.com',     
            @recipients = 'MyPeeps@SomeCompany.com',     
            @subject = @emailSubject,
            @query = 'exec abcStats @report=''full'';',
            @append_query_error = 1,
            @query_result_separator = ' ' 

        /* SMS not yet working */ 
        /*
       TODO Send a short text email that can be forwarded to SMS        
        */ 
    End 


end

I'm trying to not rewrite code. The abcStats is 300 lines of code and may grow to contain other useful alerts.

If I create the HTML in abcStats, then running it SMS will not be as useful. Can I take the result set and wrap it in HTML in the jobAbcStats? What if I rewrite abcStats to be a Table Function, then I could put the HTML in jobAbcStats? The HTML formatting only needs to be done for the email.

My previous post somewhat related: Alternative and Best ways to format SQL query for phone alert messages

score 1 · Accepted Answer · answered Aug 23 '12 at 20:03

1

You could create a @RenderFormat parameter in your abcStats sproc. Then you can rework (and probably not have to rewrite) abcStats to tweak the results based on what @RenderFormat was passed in.

This way, abcStats can spit out the bare bones SQL results when you call it from SSMS, and it can wrap the results in HTML when you use it as part of sp_send_dbmail.

answered Aug 23 '12 at 20:03

dev_etter

1,156
13
32

That might work. In that case, I would have to return the formatted HTML in a variable, pass it back to JobAlert, then pass the variable to sp_send_dbmail. – NealWalters Aug 24 '12 at 15:37

dbo.sp_send_dbmail and HTML when one email SPROC calls existing SPROC

1 Answers1