SQL syntax Advanced Application VI: How to use Try ... in SQL statements Catch

TRY ... Catch usage examples

BEGIN try   //Logical statement block End Try
BEGIN CATCH   //catch exception handling block   SET @msg = Error_message ();   PRINT @msg; END CATCH;

Use TRY ... CATCH constructs, follow these rules and recommendations:

• Each TRY ... A CATCH construct must be in a batch, stored procedure, or trigger. For example, a TRY block cannot be placed in one batch and the associated CATCH block is placed in another batch.

• The CATCH block must follow the TRY block.

• TRY ... A CATCH construct can be nested. This means that you can put the TRY ... Catch constructs are placed inside other TRY blocks and catch blocks. When an error occurs in a nested try block, program control is passed to the CATCH block associated with the nested try block.

• To handle errors that occur in a given catch block, write a TRY in the specified catch block ... CATCH block.

• TRY ... The CATCH block does not handle errors that cause the database engine to close the connection with a severity of 20 or higher. However, as long as the connection does not close, TRY ... The CATCH handles errors with a severity of 20 or higher.

• An error of severity 10 or lower is considered a warning or informational message, TRY ... The CATCH block does not handle this type of error.

• Even if the batch is located in TRY ... Within the scope of the CATCH construct, the focus message will still terminate the batch. When a distributed transaction fails, the Microsoft Distributed Transaction Coordinator (MS DTC) sends a concern message. MS DTC is used to manage distributed transactions.

Explanation of the error function

TRY ... Catch uses the following error functions to trap error messages:

• Error_number () returns the error number.

• Error_message () returns the full text of the error message. This text includes values that are provided for any replaceable parameters, such as length, object name, or time.

• Error_severity () returns the error severity.

• Error_state () returns the error status number.

• Error_line () returns the line number in the routine that caused the error.

• Error_procedure () returns the name of the stored procedure or trigger where the error occurred.

You can use these functions from the TRY ... Catch constructs a CATCH block that retrieves error information anywhere in the scope. If the error function is called outside the scope of the CATCH block, the error function returns NULL. When you execute a stored procedure in a CATCH block, you can reference the error function in the stored procedure and use it to retrieve the error information. If you do this, you do not have to repeat the error handling code in each CATCH block. In the following code example, the SELECT statement in the TRY block generates an error that is removed by 0. This error will be handled by the CATCH block, which will use the stored procedure to return an error message.

BEGIN TRY    --Generate divide-by-zero error.    SELECT 1/0; END trybegin CATCH    --Execute the error retrieval routine.    EXECUTE Usp_geterrorinfo; END CATCH; GO

Compilation errors and statement-level recompilation errors

For with TRY ... CATCH constructs errors that occur at the same execution level, TRY ... CATCH will not handle the following two types of errors:

• Compilation errors, such as syntax errors that prevent batch execution.

• An error occurred during statement-level recompilation, such as an object name resolution error after compilation due to the delay in name resolution.

When you include a TRY ... CATCH construct when a batch, stored procedure, or trigger generates one of the errors, TRY ... The CATCH construct will not handle these errors. These errors are returned to the application or batch that called the routine that generated the error. For example, the following code example shows a SELECT statement that causes a syntax error. If you execute this code in the SQL Server Management Studio Query Editor, execution will not start because the batch cannot be compiled. The error will be returned to the Query Editor and will not be made by the TRY ... Catch capture.

Use Wot_inventory; Gobegin TRY    --This PRINT statement won't run because the batch    -does not begin execution.    PRINT N ' starting execution ';    --This SELECT statement contains a syntax error,    stops the batch from compiling successfully.    SELECT * * from Wot_Inventory.dbo.InvoiceEND trybegin CATCH    SELECT         error_number () as ErrorNumber,        error_ MESSAGE () as errormessage; END CATCH; GO

Unlike the syntax errors in the preceding example, errors that occur during statement-level recompilation do not prevent the batch from compiling, but once the statement recompilation fails, it immediately terminates the batch process. For example, if a batch contains two statements and the table referenced by the second statement does not exist, deferred name resolution causes the batch to compile successfully and begin execution (without having to bind the missing table to the query plan) until the statement is recompiled. When a batch arrives at a statement that references a missing table, it stops running and returns an error. At the execution level at which the error occurred, TRY ... The CATCH construct will not handle this type of error.

For more information, see "Using TRY in Transact-SQL ..." in working with Database engine errors. CATCH "section.

PS: Welcome to scan the QR code below or click on the link, join QQ group

SQL syntax Advanced Application VI: How to use Try ... in SQL statements Catch