Try catch usage in SQL

Try catch in SQL

The code is as follows:	Copy code
BEGIN TRANSACTION BEGIN TRY -- YOUR SQL COMMIT END TRY BEGIN CATCH ROLLBACK END CATCH

The above code is executed in SQL SERVER 2008.

After try catch is used, the CATCH will be rolled back if an exception occurs.

Each TRY... CATCH structure must be in a batch, stored procedure, or trigger. For example, TRY blocks cannot be placed in one batch but the associated CATCH blocks cannot be placed in another batch. The following script generates an error:

Copy
 

The code is as follows:	Copy code
BEGIN TRY SELECT * FROM sys. messages WHERE message_id = 21; END TRY GO -- The previous GO breaks the script into two batches, -- Generating syntax errors. The script runs if this GO -- Is removed. BEGIN CATCH SELECT ERROR_NUMBER () AS ErrorNumber; End catch; GO

The CATCH block must be followed by the TRY block.

TRY... The CATCH structure can be nested. This means that TRY... The CATCH structure is placed in other TRY blocks and CATCH blocks. When an error occurs in the nested TRY block, the program control will be passed to the CATCH block associated with the nested TRY block.

To handle errors in a given CATCH block, write the TRY... CATCH block in the specified CATCH block.

If the TRY... CATCH block is not processed, the database engine closes the connection and the severity is 20 or higher. However, as long as the connection is not closed, TRY... CATCH will handle errors with a severity of 20 or higher.

An error with a severity of 10 or lower is considered as a warning or informative message. TRY... CATCH block does not handle this type of error.

Even if the batch processing is within the scope of TRY... CATCH, the message of interest terminates the batch processing. When a distributed transaction fails, Microsoft distributed transaction coordinator (ms dtc) sends a Follow message. Ms dtc is used to manage distributed transactions.

Within the scope of the CATCH block, you can use the following system functions to obtain the error message that causes the CATCH block to be executed:

ERROR_NUMBER () returns the error number.

ERROR_SEVERITY () returns the severity.

ERROR_STATE () returns the error status number.

ERROR_PROCEDURE () returns the name of the stored procedure or trigger with an error.

ERROR_LINE () returns the row number in the routine that causes the error.

ERROR_MESSAGE () returns the complete text of the error message. This text can include any value provided by replaceable parameters, such as length, object name, or time.

-----------------------------------------------------------

The code is as follows:	Copy code
BEGIN TRY BEGIN TRANSACTION ----------------------------////////////////////////////// COMMIT TRANSACTION END TRY BEGIN CATCH Rollback DECLARE @ ErrMsg nvarchar (4000), @ ErrSeverity int SELECT @ ErrMsg = ERROR_MESSAGE (), @ ErrSeverity = ERROR_SEVERITY () RAISERROR (@ ErrMsg, @ ErrSeverity, 1) END CATCH