A Mock Object (method) for sp_send_dbmail

It can be messy to test MS SQL Server 2005 Database Mail in your stored procedure because often you don't want to spam real people. So, often I found myself just putting some limited print statements and commenting in and out this code depending if I was testing.

I generally like adding if-else statements that key off of a test flag to permanently have the code available for testing. This helps when I sometimes need to make sure it is still working in production.

In eithe case, I still had a lot of duplicated code that I didn't like to look at and I was writing that debug code all the time.

My solution (adapted from unit testing principle that says we should use Mock Objects to simulate external systems when we do unit tests) is to create a method that takes the parameters that I typically use for sp_send_dbmail and does nothing more than print out their values. This allows me to use the exact same code when debugging as when I actually send the mail.

Here is my mock stored procedure:

create proc sp_send_dbmail_mock_object
(
 @profile_name nvarchar(128),
 @recipients varchar(MAX),
 @blind_copy_recipients varchar(MAX) = null,
 @subject nvarchar(255),
 @body nvarchar(MAX),
 @mailitem_id int = null output
)
as
 print char(13) + char(10) + 'sp_send_dbmail_mock_object called: '
 print 'Recipients: ' + ISNULL(@recipients, 'NULL')
 print 'Blind Copy Recipients: ' + ISNULL(@blind_copy_recipients, 'NULL')
 print 'Subject: ' + ISNULL(@subject, 'NULL')
 print 'Body: ' + ISNULL(@body, 'NULL')
 Set @mailitem_id = -1
 
go

I can now use it something like this

if @ActuallySendEmail = 1
BEGIN

 -- Docs: http://msdn.microsoft.com/en-us/library/ms190307.aspx
 EXEC msdb.dbo.sp_send_dbmail
   @profile_name = @Profile,
   @recipients = @RowRecipients,
   @blind_copy_recipients = @RowBlind_copy_recipients,
   @subject = @TemplateSubject,
   @body = @RowBody,
   @mailitem_id = @RowMailItemID output
END
else
BEGIN
 -- print debug info
 EXEC sp_send_dbmail_mock_object
   @profile_name = @Profile,
   @recipients = @RowRecipients,
   @blind_copy_recipients = @RowBlind_copy_recipients,
   @subject = @TemplateSubject,
   @body = @RowBody,
   @mailitem_id = @RowMailItemID output
END