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.
[code lang=”sql”]
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
[/code]