Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error handling in while loop...skip if failure Expand / Collapse
Author
Message
Posted Sunday, January 6, 2013 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 9:29 PM
Points: 4, Visits: 22
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.

Post #1403316
Posted Sunday, January 6, 2013 3:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 223, Visits: 1,719
Remove set XACT_ABORT ON and implement TRY CATCH http://msdn.microsoft.com/en-us/library/ms175976%28v=sql.100%29.aspx, so this proc should looks like (I can't test it so first test it):
CREATE PROCEDURE [dbo].[proc_Master_Schedule] 	

AS


-- set XACT_ABORT ON --line to remove

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

--TRY CATCH block
BEGIN TRY
EXEC dbo.proc_Schedules @ProgramID
END TRY
BEGIN CATCH

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 CATCH;
--end of TRY CATCH block

SET @LoopCount=@LoopCount + 1
END

DROP TABLE #tmpProgram

Post #1403318
Posted Monday, January 7, 2013 6:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 9:29 PM
Points: 4, Visits: 22
Thanks for your reply.

The TRY...CATCH block worked so when the stored procedure failed for one program then it skipped to the next one and sent the email. I also used the Error Functions from Try...Catch to print the error no, proc name and error message.

Once again thank you for your suggestion.
Post #1403567
Posted Monday, January 7, 2013 7:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 223, Visits: 1,719
I'm glad I could help
Post #1403597
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse