SQL Server – Best Practices for sp_send_dbmail Usage

I would suggest one of the below:

Method 1

1. For each error condition, create an inline, table-valued user-defined function which generates the erroneous results.
2. Make a table which contains all of the function names, with a field for the email recipients.
3. Build a procedure (MailErrorResults) which takes a function name as a parameter. If will retrieve the email recipients from the table and execute msdb.dbo.sp_send_dbmail, sending in 'SELECT * FROM ' + FunctionName + '();' as the query for the attachment.

4. Build a procedure (MailErrorResultsAll) which uses a cursor to loop over the table with the function names. In the loop, you will execute dynamic SQL along these lines:

   'IF EXISTS(SELECT * FROM ' + FunctionName + '()) BEGIN EXEC MailErrorResults @FunctionName = ' + @FunctionName + '; END'

(Beware of SQL injection attacks possible by adjusting the FunctionName values in the table.)

You could omit building the separate procedure MailErrorResults, but then you are writing the entire msdb.dbo.sp_send_dbmail call in dynamic SQL, which is ugly and error prone.

You could avoid MailErrorResults hitting the function table again by sending it everything it needs (e.g. the recipients), but your dynamic SQL in MailErrorResultsAll would become more complicated, and the function table will likely be too small for this to matter. This is especially true if you end up having more information in the function table, such as a field describing the problem along with information on how to fix it.

The advantages of this method are that (1) the SQL is only written once; (2) the error detection SQL can be written using validation (no dynamic SQL quotation mark issues); (3) a reusable execution plan can be generated; and (4) eventually the check can be embedded into your application logic. The disadvantage is that the query is run twice for each check-- to see if there are errors and then during the error sending process. That disadvantage can be mitigated with Method 2.

Method 2

You would essentially reproduce Method 1, but you could eliminate the double call to the function by creating a permanent table to store the error results. Your dynamic SQL would look something like this:

'INSERT INTO ErrorList (ErrorRunNumber, a, b, c) SELECT ' + CONVERT(varchar(50),@ErrorRunNumber) + ', a, b, c FROM ' + @FunctionName + '();'

You would have a separate table called ErrorRuns which would store each execution of this process for a particular function. You wouldn't need two procedures. Your single procedure would loop over the function table, running the above dynamic SQL for each function (getting a separate run number for each, of course), and then your msdb.dbo.sp_send_dbmail call would be passed a query like this:

'SELECT a, b, c FROM ErrorList WHERE ErrorRunNumber = ' + CONVERT(varchar(50),@ErrorRunNumber) + ';'

This trades storage space and extra writes for the double reads of the existence check in Method 1. This could be superior if your error detection is resource intensive.

Method 3

One other option, which I don't recommend, is to simply put the raw SQL statements into a functions table. You could then pass them into dynamic SQL without worrying about the single quote issues, because they could be retrieved from the table as native strings. I would only explore this if you had an aversion to user-defined functions for some reason.

For example, if your end users were really writing the queries themselves and did not have user-defined function creation rights, you might have to explore this.

Another twist to this method would be to have the procedure responsible for saving the user-specific SQL execute dynamic SQL to create the user-defined function, and then use one of the methods above.

The easier you make it for end users to execute their own SQL in your environment, though, the more security concerns you will have.

@reply_to can only accept 1 email address. Its by design - see this connect.

The document reflects that as well.

[ @reply_to= ] 'reply_to' Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP secuirty settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.