Create job to send alert using sp_send_dbmail when table value greater than x

PROBLEM

The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached.

TABLE CREATE

CREATE TABLE [dbo].[SMSCounter](
  [Counter] [int] NOT NULL,
  [CounterDate] [datetime] NOT NULL )

EXAMPLE DATA

380, 2012-09-10 00:00:00.000
177, 2012-10-14 00:00:00.000
999, 2012-11-16 00:00:00.000

JOB SCRIPT

The script below is my attempt to create a scheduled job that will check every hour if the SMScounter.Counter is greater than 950.

USE DATABASEname
select (Counter) as CHECKVALUE from SMSCounter where smscounter.CounterDate  = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)    
IF CHECKVALUE > 950

BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near', 
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =
    'USE DATABASEname
     (select * from SMSCounter where CounterDate = GETDATE())'

END

QUESTIONS

-
How can I format GETDATE to examine dd/mm/yy and ignore the time part when reading SMScounter.CounterDate? (script above edited , works now).

-
How can I get the IF statement to work to compare CHECKVALUE to 950?

I'm using SQL Server 2008 standard edition.

You can use convert (date, GETDATE()) to get just the 'date' part:

select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16

And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it.

For an IF statement you need to pass in a value to be checked, and then wrap the code to be run within BEGIN and END statements. Here's an example using the sysobjects table:

declare @cnt integer
select @cnt=COUNT(1) from sysobjects

if @cnt>50
begin
  select 'hello'
end

which will say hello if you've more than 50 objects in your DB

So in your case:

USE DATABASEname
go
declare @counter integer

select @counter=MAX([Counter]) from SMSCounter where convert(date,CounterDate) = convert(date,GETDATE())

IF @counter > 950
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =  '(select * from DATABASEname.dbo.SMSCounter where convert(date,CounterDate) = convert)(Date,GETDATE()))'
END

I've added a MAX function to your check query, as this will only return a single row containing the maximum value for that day, otherwise it'll return a random value from all the matching rows. If you're overwriting the day's value each time you write to the table, then it's superfluous.

StackExchange Database Administrators Q#28780, answer score: 5