I have the following stored procedure that runs as a job:
CREATE PROCEDURE [dbo].[proc_Master_Schedule]
set XACT_ABORT ON
DECLARE @Count INT
DECLARE @LoopCount INT
DECLARE @ProgramID INT
DECLARE @strMessage NVARCHAR(MAX)
DECLARE @subject NVARCHAR(MAX)
SELECT IDENTITY(INT,1,1) ID, [ProgramID] INTO #tmpProgram
FROM dbo.Schedule_Setup WITH (NOLOCK)
SELECT @Count = @@RowCount
SET @LoopCount = 1
WHILE @LoopCount <= @Count
WHERE ID = @LoopCount
EXEC dbo.proc_Schedules @ProgramID
IF @@Error <> 0
SET @Message = 'The Schedule for Program ' + CONVERT(NVARCHAR,@ProgramID) + 'failed.'
@sender_email = 'SQL2008@test.com',
@Message = @Message,
SET @LoopCount=@LoopCount + 1
DROP 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.