SQL Server – Email Results of Stored Procedure Conditionally

ben himself's picture

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.

-- Use ## to make it a global table so that send_mail can "see" it
CREATE TABLE ##People ( [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

Tags: 

Categories: