SQL Server – Email Results of Stored Procedure Conditionally

Within a Stored Procedure, you may want the results of a particular query emailed to you, but only if there is a result.  In other words, I don’t want to be notified when the result set size is 0.

CREATE TABLE ##People(  -- Use ## to make it a global table so that send_mail can "see" it
[Name] varchar(100),[Address] varchar(100)
)

INSERT INTO ##People
SELECT [Name],[Address] FROM some_table

DECLARE @the_count int
SELECT @the_count = COUNT(*) FROM ##People

IF @the_count > 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@query = 'SELECT * FROM ##People',
@recipients = 'email@domain.com',
@body = 'These people were selected:

',
@subject = 'People selected'
END
DROP TABLE ##People

END