You are here

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.

-- 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
Categories: 
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
11 + 6 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer