ERROR HANDLING

1. ERROR HANDLING JanardanPandey VenkateshNadar

2. ERRORS??.....to be simple… • Errors are the things which are not a part of our result set. • Errors are returned to applications through an error-handling mechanism that is separate from the processing of result sets. • Each database application programming interface (API) has a set of functions, interfaces, methods, objects, or structures through which they return errors and messages.

3. To be more specific…. • Each API function or method typically returns a status code indicating the success of that operation. • If the status is anything other than success, the application can call the error functions, methods, or objects to retrieve the error information.

4. Each and every component of the sql server can raise their own error statements or messages..

5. Tackling with errors… • Errors in Transact-SQL code can be processed by using aTRY…CATCHconstruct similar to the exception-handling features of the Microsoft Visual C++ and Microsoft Visual C# languages. • This try and catch mechanism could be further divided as follows…

6. TRY AND CATCH BLOCKS…

7. KNOWING MORE… • After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement. • If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.

8. Transact-SQL statements in the TRY block following the statement that generates an error will not be executed. • If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.

9. GOING SYNTACTICAL BEGIN TRY EXECUTE Example1; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; EXECUTE Example2;

10. NOTE • A TRY block must be immediately followed by a CATCH block. • TRY…CATCH constructs can be nested. This means that TRY…CATCH constructs can be placed inside other TRY and CATCH blocks. When an error occurs within a nested TRY block, program control is transferred to the CATCH block that is associated with the nested TRY block.

11. To handle an error that occurs within a given CATCH block, write a TRY…...CATCH block within the specified CATCH block.

12. ERROR FUNCTIONS… • TRY…CATCH uses the following error functions to capture error information: • ERROR_NUMBER() returns the error number. • ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times. • ERROR_SEVERITY() returns the error severity.

13. ERROR FUNCTIONS CONTINUED • ERROR_STATE() returns the error state number. • ERROR_LINE() returns the line number inside the routine that caused the error. • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

14. THANK YOU