If you have a 10 SQL statements in a stored procedure, where 10th statement is an update statement which updates time in a table indicating that on that particular time that stored procedure got executed successfully.
Lets consider a situation where 5th statement got run-time error which leads incorrect results in stored procedure.
What happens now, ?
Will the rest of the statements executes till the 10th statements and causing the update to a table saying the job got executed successfully ? The answer is YES
In real scenario this should not happen when ever there is an run-time error the JOB needs to stop at that moment
Why this background ?
To stop the execution of the job at that moment of run-time error we use
SET XACT_ABORT ON
Before the starting of the Stored Procedure
SET XACT_ABORT OFF
After the last statement of the Stored Procedure.
Lets consider a situation where 5th statement got run-time error which leads incorrect results in stored procedure.
What happens now, ?
Will the rest of the statements executes till the 10th statements and causing the update to a table saying the job got executed successfully ? The answer is YES
In real scenario this should not happen when ever there is an run-time error the JOB needs to stop at that moment
Why this background ?
To stop the execution of the job at that moment of run-time error we use
SET XACT_ABORT ON
Before the starting of the Stored Procedure
After the last statement of the Stored Procedure.
No comments:
Post a Comment