CODEWITHSUNDEEP
sqlsql-server
JeffreyLazo
JeffreyLazo

Reputation: 853

SQL Email based on query

I have set up SQL server to email and have successfully. I am trying to send an email with the following query:

select count(*) from report queue

I would like an email sent when count is > 10.

I have never done a SQL stored procedure before.

Upvotes: 0

Views: 91

Answers (4)

Anon
Anon

Reputation: 10908

CREATE PROCEDURE [dbo].[email_report] (@recipients nvarchar(max), @subject nvarchar(max) = '', @body nvarchar(max) = '')
AS
BEGIN
  IF (SELECT COUNT(*) FROM [report_queue]) > 10
  EXEC [msdb].[dbo].[sp_send_dbmail]
    @recipients = @recipients,
    @subject = @subject,
    @body = @body;   
END

More details available here: sp_send_dbmail (Transact-SQL)

Upvotes: 0

William
William

Reputation: 6610

Another alternative;

IF ((SELECT COUNT(*) FROM [report queue]) > 10)

SELECT * FROM [report queue]

Upvotes: 0

Barmar
Barmar

Reputation: 780889

SELECT COUNT(*) AS ct
FROM report_queue
HAVING ct > 10

Upvotes: 1

Lajos Veres
Lajos Veres

Reputation: 13725

Can you try this?

select email from report queue group by email having count(*)>10

Upvotes: 0

Related Questions