Hello,
I have the following stored procedure that runs as a job:
CREATE PROCEDURE [dbo].[proc_Master_Schedule]
AS
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)
WHERE Deleted=0
SELECT @Count = @@RowCount
SET @LoopCount = 1
WHILE @LoopCount <= @Count
BEGIN
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 + 1
END
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.
Thank you.
Error handling in while loop...skip if failure
Posted - 01/06/2013 : 04:23:27
