Hello,I have the following stored procedure that runs as a job: CREATE PROCEDURE [dbo].[proc_Master_Schedule] AS set XACT_ABORT ONDECLARE @Count INTDECLARE @LoopCount INTDECLARE @ProgramID INTDECLARE @strMessage NVARCHAR(MAX)DECLARE @subject NVARCHAR(MAX)SELECT IDENTITY(INT,1,1) ID, [ProgramID] INTO #tmpProgram FROM dbo.Schedule_Setup WITH (NOLOCK) WHERE Deleted=0SELECT @Count = @@RowCountSET @LoopCount = 1WHILE @LoopCount <= @CountBEGIN SELECT @ProgramID=ProgramID FROM #tmpProgram WHERE ID = @LoopCount EXEC dbo.proc_Schedules @ProgramID IF @@Error <> 0 SET @Message = 'The Schedule for Program ' + CONVERT(NVARCHAR,@ProgramID) + 'failed.' exec Master.dbo.sp_sendSAM @recipients='Dev.Team@test.com', @subject='proc_Master_Schedule failure', @sender_email = 'SQL2008@test.com', @Message = @Message, @format ='html' END SET @LoopCount=@LoopCount + 1ENDDROP TABLE #tmpProgram
I would like the job to continue to run when there is a failure in executing the stored procedure for any of the ProgramIDs and also send an email that it failed for that specific ProgramID. So should the procedure fail for one ProgramID, then skip to the next ProgramID and notify. Also I need to have better error handling.Any help with regards to this matter will be greatly appreciated.Thank you.