Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mallen
Starting Member
27 Posts |
 Posted - 2011-02-22 : 14:52:31
|
| I am trying to put an email into a case statement.The procedure should check to see if another procedure completed successfully for the night. If so it runs the next procedure. If NOT I would like it to send me an email.It is returning the error 'Incorrect syntax near the keyword 'EXEC' and Incorrect syntax near the keyword 'ELSE'. Everything works perfect if I take out the email EXEC statements and just throw in text so there is something wrong with how I am writing the email part. How should I do this correctly?This is what I have:-- Capture date of most recently loaded resultsDECLARE @Date datetimeSET @Date = (SELECT TOP 1 DateEntered FROM ReturnFile ORDER BY DateEntered DESC)-- Capture process status of moce recently loaded resultsDECLARE @PStatus bitSET @PStatus = (SELECT TOP 1 IsProcessed FROM ReturnFile ORDER BY DateEntered DESC)-- Check to ensure results have been processed.SELECTCASE WHEN ((CONVERT(VARCHAR(10),@Date,111)) = (CONVERT(VARCHAR(10),GETDATE(),111))) AND @PStatus=1 THEN 'Run written procedure here' WHEN ((CONVERT(VARCHAR(10),@Date,111)) = (CONVERT(VARCHAR(10),GETDATE(),111))) AND @PStatus=0 THEN EXEC msdb.dbo.sp_send_dbmail @recipients='procedures@xxx.com', @subject = 'TS Results Test', @body = 'TS Results are not finished processing'; ELSE EXEC msdb.dbo.sp_send_dbmail @recipients='procedures@xxx.com', @subject = 'TS Results Test', @body = 'TS Results did not load';END |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Email with Case statement