SQLTeam.com | Weblogs | Forums

IF Record is in Table Send email


#1

I have a SQL job scanning a table every 5 min to see if a record hits a table and if so it fires and email. But my Syntax isn't working.

BEGIN

declare @recordCount int

select @recordCount = isnull(count(*), 0)
from myqueue

IF (@recordCount > 0)
begin

EXEC msdb.dbo.sp_send_dbmail


#2

A bit simpler version:

IF NOT EXISTS(SELECT 1 FROM myqueue) RETURN;
EXEC msdb.dbo.sp_send_dbmail ...

If no records exist, it returns immediately, otherwise it sends the email. EXISTS() is more efficient than performing a count, if all you want to know is if rows exist.


#3

Excellent answer, but why not simply:

IF EXISTS(SELECT 1 FROM myqueue)
    EXEC msdb.dbo.sp_send_dbmail ...

#4

That works too. It's up to the submitter's preference.

I prefer to "fail early" with individual conditions per line of code:

IF condition1 return;
IF NOT condition2 return;
.... all other failure modes listed
DO THESE ACTIONS  -- since all other conditions/tests have "passed"

The reason is that each condition can be added/removed/commented out separately; the only conditional action taken is to RETURN; and no need to use BEGIN...END constructs. This leads to greater consistency.

I've found that "affirmative" conditions tend to get more elaborate with AND and OR conditions, and mixing those without proper parentheses groups leads to logic errors. It's better IMHO to indicate which conditions are failure modes so that they are documented in code, and are often simpler than affirmative conditions.

This also helps with unit testing to provide failing test modes, RETURN can be replaced with some kind of assertion.