SQL – Information of TRY, CATCH and Error Handling

In SQL Deadlocks are very crucial things which we need to handle with ease, So for this features, You can do whatever if any error occur.
@@ERROR is not used for every statement executed then.
TRY and CATCH helps to write logic and error handling code differenly.
Whatever code need to execute you can write in TRY block and if any error occur from code block then you can handle in CATCH block.
In case the code within the TRY block fails,
the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution.
Also in CATCH block it provide information related to Error ID, message, state of error and transaction state.

Below are the Functions used in CATCH block :

ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
ERROR_STATE: returns the state number of the error.
ERROR_LINE: returns the line number where the error occurred.
ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Syntax:
BEGIN TRY
{
SQL Code Block
}
END TRY
BEGIN CATCH
{
SQL Block
}
END CATCH

The TRY or CATCH block can contain a single T-SQL statement or a series of statements.
The CATCH block must follow immediately after the TRY block.
The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Example of TRY…CATCH:

BEGIN TRY
DECLARE @X INT
—- String to binary convertion error
SET @X = ‘1X’
PRINT ‘TRY block’
END TRY
BEGIN CATCH
PRINT ‘Error Occured : ‘+ ERROR_MESSAGE()
END CATCH
PRINT ‘After Completion TRY CATCH blocks’

Above code will return following result:

Error Occured
After Completion TRY CATCH blocks

If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block,
but instead skips over the CATCH block and executes the first statement following the END CATCH statement.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: