The Implementing Error Handling module provides you with the instructions and devices to develop your hands on skills in the following topics.
- Working with error handling blocks
- Working with THROW statement
- Working with RAISERROR statement
- Using TRY…CATCH block in SQL stored procedure
The following exam objectives are covered in this lab:
- Write and execute a query using TRY…CATCH error handling block with an error in the TRY block
- Write and execute a query using TRY…CATCH error handling without any error in the TRY block and observe where the control gets passed after the TRY block
- Write and execute a query using THROW statement to generate an error message in a TRY…CATCH error handling block
- Write and execute a query using RAISERROR statement to generate an error message in a TRY…CATCH error handling block
- Write a stored procedure with TRY…CATCH block
Exercise 1 - Working with Error Handling Blocks
SQL Server provides you with the TRY...CATCH block to handle errors in your Transact-SQL code. The TRY...CATCH block has two parts, namely, a TRY block and a CATCH block. You can place Transact-SQL statements inside the TRY block that can cause error conditions. You then place error-processing code inside the CATCH block. When SQL Server detects an error condition inside the TRY block, the system transfers the control to the first statement inside the associated CATCH block. After all the statements inside the CATCH block are executed, the system transfers the control to the first statement after the END CATCH statement.
In this exercise, you will learn to handle errors in Transact-SQL code using the TRY…CATCH block.
Exercise 2 - Working with THROW Statement
You can use the THROW statement to raise an exception and transfer the control to the associated CATCH block in a TRY...CATCH construct. The THROW statement accepts an error number, a string message, and a state as arguments. The error number denotes the exception and must be an int data type. The error number must be greater than or equal to 50000 and less than or equal to 2147483647. The string message describes the exception that is being thrown. The state must be a tinyint data type that can be a constant or a variable between 0 and 255. This argument represents the state to associate with the error message. Please consider the following points when using the THROW statement:
The statement preceding the THROW statement must be followed by a statement terminator (;). You can use the THROW statement without specifying the parameters; however, a THROW statement without arguments must be used within a CATCH block. If you do not provide a TRY...CATCH statement, the THROW statement ends the session.
In this exercise, you will learn to use the THROW statement to generate an error in a TRY…CATCH error handling block.
Exercise 3 - Working with RAISERROR Statement
You can use the RAISERROR statement to generate user-defined messages and return these messages to the calling application or to the associated CATCH block of a TRY...CATCH construct.
The user-defined messages generated by the RAISERROR statement uses the same format as a system error or warning message raised by the SQL Server Database engine. The RAISERROR statement can reference a user-defined message available in the sys.messages catalog view. Alternately, the statement can also generate messages dynamically.
In this exercise, you will write and execute a query using the RAISERROR statement to generate a user-defined error message in a TRY...CATCH error handling block.
Exercise 4 - Using TRY…CATCH Block in SQL Stored Procedure
You can use TRY...CATCH error handling blocks in stored procedures. When an error is encountered in a TRY block placed within a stored procedure, the control passes to the first statement in the associated CATCH block. After executing all the statements in the associated CATCH block, the control passes to the calling statement of the stored procedure if END CATCH is the last statement in the stored procedure.
In this exercise, you will write and execute a stored procedure using a TRY...CATCH error handling block.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.