I am trying to create a TSQL script that can loop through each record in the table and send email notification if the Run_Status is Error.The email message should include the Run_Status and the File_Name results. Such as Deal B is Error.. The below TSQL script only displays the first record and it seems that it does not loop through other records.
DECLARE @RunStatus varchar(25)DECLARE status_cursor CURSOR FOR SELECT Run_Date, File_Name, Record_Count, Run_Status, Run_Remarks FROM dbo.Daily_Check_LogOPEN status_cursorSet @RunStatus = nullFETCH NEXT FROM status_cursorIF @RunStatus = 'Error'BEGINPrint 'Error'EXEC msdb.dbo.sp_send_dbmail@profile_name = 'JobsNotifications',@recipients = 'my@email.com',@subject = 'Test',@body = 'Records Not Found.'ENDELSEPrint 'Success' CLOSE status_cursor;DEALLOCATE status_cursor;
My table records are shown as Run_Date File_Name Record_Count Run_Status Run_Remarks2013-02-16 Deals B 1902 Success Records Found 2013-02-17 Deals 0 Error No Record 2013-02-18 Deals A 2964 Success Records Found 2013-02-19 Deals A 0 Error No Record
