Send email when a field is 0

Question

Send email when a field is 0

hoseam

SSCertifiable

Points: 5533
More actions
October 30, 2017 at 1:35 pm

#326033

Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247120 More actions · Answer 1

hoseam - Monday, October 30, 2017 1:35 PM
Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help

sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?

hoseam SSCertifiable Points: 5533 More actions · Answer 2

Phil Parkin - Monday, October 30, 2017 2:01 PM
hoseam - Monday, October 30, 2017 1:35 PM
Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?

Yes please, a conditional logic.

hoseam SSCertifiable Points: 5533 More actions · Answer 3

Phil Parkin - Monday, October 30, 2017 2:01 PM
hoseam - Monday, October 30, 2017 1:35 PM
Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?

I tried this but it throws errors when I get more than one isProcessed

IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0 BEGIN

EXEC msdb.dbo.sp_send_dbmail @recipients= @EmailAddress, @copy_recipients= @EmailAddressCC, @subject=@MailSubject, @body=@BodyMessage, @body_format='HTML', @from_address='Sender Name <NoReply@myemail.com>', @reply_to='NoReply@myemail.com'

END

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 4

hoseam - Monday, October 30, 2017 11:39 PM
Phil Parkin - Monday, October 30, 2017 2:01 PM
hoseam - Monday, October 30, 2017 1:35 PM
Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?
I tried this but it throws errors when I get more than one isProcessed
IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0     BEGIN
    EXEC msdb.dbo.sp_send_dbmail         @recipients= @EmailAddress,         @copy_recipients= @EmailAddressCC,         @subject=@MailSubject,         @body=@BodyMessage,         @body_format='HTML',         @from_address='Sender Name <NoReply@myemail.com>',         @reply_to='NoReply@myemail.com'
    END

I'm not quite sure I follow your logic, but I think if you change the IF statement as follows:
IF EXISTS (SELECT * FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE) AND IsProcessed > 0)

Thomas Rushton
blog: https://thelonedba.wordpress.com

sgmunson SSC Guru Points: 110640 More actions · Answer 5

ThomasRushton - Tuesday, October 31, 2017 4:43 AM
hoseam - Monday, October 30, 2017 11:39 PM
Phil Parkin - Monday, October 30, 2017 2:01 PM
hoseam - Monday, October 30, 2017 1:35 PM
Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?
I tried this but it throws errors when I get more than one isProcessed
IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0     BEGIN
    EXEC msdb.dbo.sp_send_dbmail         @recipients= @EmailAddress,         @copy_recipients= @EmailAddressCC,         @subject=@MailSubject,         @body=@BodyMessage,         @body_format='HTML',         @from_address='Sender Name <NoReply@myemail.com>',         @reply_to='NoReply@myemail.com'
    END
I'm not quite sure I follow your logic, but I think if you change the IF statement as follows:
IF EXISTS (SELECT * FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE) AND IsProcessed > 0)

Understand that this change means one e-mail, regardless of the number of conditions that occur, and it doesn't identify the "offending rows".

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 6

sgmunson - Tuesday, October 31, 2017 11:28 AM
ThomasRushton - Tuesday, October 31, 2017 4:43 AM
hoseam - Monday, October 30, 2017 11:39 PM
Phil Parkin - Monday, October 30, 2017 2:01 PM
hoseam - Monday, October 30, 2017 1:35 PM
Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?
I tried this but it throws errors when I get more than one isProcessed
IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0     BEGIN
    EXEC msdb.dbo.sp_send_dbmail         @recipients= @EmailAddress,         @copy_recipients= @EmailAddressCC,         @subject=@MailSubject,         @body=@BodyMessage,         @body_format='HTML',         @from_address='Sender Name <NoReply@myemail.com>',         @reply_to='NoReply@myemail.com'
    END
I'm not quite sure I follow your logic, but I think if you change the IF statement as follows:
IF EXISTS (SELECT * FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE) AND IsProcessed > 0)
Understand that this change means one e-mail, regardless of the number of conditions that occur, and it doesn't identify the "offending rows".

sp_send_dbmail can either include a query result in the body or attach the result set as a file, which might work if the goal is to get a work list for someone to deal with.

sstanek 27058 SSC Enthusiast Points: 142 More actions · Answer 7

Knowing which was a problem is a job for the @BodyText, this is a variant on something I use (since it looks like you are already using html type)

DECLARE @Res VARCHAR(MAX)

SELECT @Res = COALESCE(@Res , '') +
'<tr>'+
'<td text-align: center;> '+[id]+' </td>'+
'<td text-align: center;> '+[Processname]+' </td>'+
'<td text-align: center;> '+[isProcessed]+' </td>'+
'<td text-align: center;> '+[isSuccess]+' </td>'+
+ '</tr>'
FROM <YourTableName>

Select @Bodytext = '<!DOCTYPE html>
<html>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
text-align: right;
}
th, td {
padding: 5px;
}
</style>
<body>
'+
'<table border: 1px solid black;><tr> <th> Id </th><th> Processname </th><th> isProcessed </th><th> isSuccess </th>'
+@Res
+'</table>'
+'</body>
</html> '

Where your <YourTableName> is whatever you set it to be in the initial query you gave...

Send email when a field is 0

Cookies on SQLServerCentral