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
