May 5, 2008 at 12:32 pm
Depending on whether the couter is 0 or above 0 then I want a different email sending. Can anyone help with this?
SELECT ISNULL(Count(Dial.LastCalled),0) As [Counter]
CASE [Counter]
WHEN 0 THEN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = 'alasdair.thomson@voicegroup.co.uk',
@body = 'Al It Works! No Sales Were Made Today',
@subject = 'SQL 2005 Database Mail - Alasdair Is Testing';
WHEN > 0 THEN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = 'alasdair.thomson@voicegroup.co.uk',
@body = 'Al It Works! Lots of Sales Were Made Today',
@subject = 'SQL 2005 Database Mail - Alasdair Is Testing';
ELSE
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = 'alasdair.thomson@voicegroup.co.uk',
@body = 'An error has occured Please check Data',
@subject = 'SQL 2005 Database Mail - Alasdair Is Testing';
FROM cmp_HiddenHearingOverall INNER JOIN
Dial ON cmp_HiddenHearingOverall.DialID = Dial.DialID
WHERE ((cmp_HiddenHearingOverall.LastCRC = 'AgBAP') OR
(cmp_HiddenHearingOverall.LastCRC = 'AgHAP')) AND (CONVERT(Varchar, dbo.Dial.LastCalled, 103) = CONVERT(Varchar, GETDATE(), 103))
Error message is:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'FROM'.
May 5, 2008 at 1:05 pm
Okay, modified a bit after looking at examples on the net...
IF (SELECT ISNULL(Count(Dial.LastCalled),0) As [Counter] FROM cmp_HiddenHearingOverall INNER JOIN Dial ON cmp_HiddenHearingOverall.DialID = Dial.DialID WHERE ((cmp_HiddenHearingOverall.LastCRC = 'AgBAP') OR (cmp_HiddenHearingOverall.LastCRC = 'AgHAP')) AND (CONVERT(Varchar, dbo.Dial.LastCalled, 103) = CONVERT(Varchar, GETDATE(), 103))) = 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = 'alasdair.thomson@voicegroup.co.uk',
@body = 'Al It Works! No Sales Were Made Today',
@subject = 'SQL 2005 Database Mail - Alasdair Is Testing';
Else
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = 'alasdair.thomson@voicegroup.co.uk',
@body = 'Al It Works! Lots of Sales Were Made Today',
@subject = 'SQL 2005 Database Mail - Alasdair Is Testing';
END
But I get the following error message;
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'END'.
So I know i'm on the right lines, please can you help me further?
May 5, 2008 at 3:47 pm
You don't need [font="Courier New"]END[/font] with an [font="Courier New"]IF[/font] statement, only with the [font="Courier New"]CASE[/font] statement. Unless you are doing multi-line code blocks for the statement blocks. So you could have:
[font="Courier New"]IF something = somethingelse
run statement
ELSE
run some other statement[/font]
Or you could have:
[font="Courier New"]IF something = somethingelse
BEGIN
declare variable
run statement
run another statement
END
ELSE
BEGIN
declare variable
run statement
END
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
March 13, 2017 at 3:44 pm
IF (--statement here)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '--db mail profile',
@recipients = '--email addresses',
@subject = '--email subject',
@body = '--email body',
@execute_query_database = '--db name',
@query = '-query for email'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply