Exception Handling -- Sql server

    Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

Syntax

BEGIN TRY

     { sql_statement | statement_block }

END TRY

BEGIN CATCH

     [ { sql_statement | statement_block } ]

END CATCH

[ ; ]

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement. If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.

When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

TRY…CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY…CATCH constructs. For example, a CATCH block can contain an embedded TRY…CATCH construct to handle errors encountered by the CATCH code.

Errors encountered in a CATCH block are treated like errors generated anywhere else. If the CATCH block contains a nested TRY…CATCH construct, any error in the nested TRY block will pass control to the nested CATCH block. If there is no nested TRY…CATCH construct, the error is passed back to the caller.

TRY…CATCH constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY block. Alternatively, the stored procedures or triggers can contain their own TRY…CATCH constructs to handle errors generated by their code.

For example, when a TRY block executes a stored procedure and an error occurs in the stored procedure, the error can be handled in the following ways:

• If the stored procedure does not contain its own TRY…CATCH construct, the error returns control to the CATCH block associated with the TRY block that contains the EXECUTE statement.
• If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.

GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

The TRY…CATCH construct cannot be used in a user-defined function.

Retrieving Error Information:

In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

• ERROR_NUMBER() returns the number of the error.
• ERROR_SEVERITY () returns the severity.
• ERROR_STATE () returns the error state number.
• ERROR_PROCEDURE () returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE () returns the line number inside the routine that caused the error.
• 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.

 

These functions return NULL if they are called outside the scope of the CATCH block. Error information can be retrieved by using these functions from anywhere within the scope of the CATCH block.

For example, the following script shows a stored procedure that contains error-handling functions. In the CATCH block of a TRY…CATCH construct, the stored procedure is called and information about the error is returned. 

Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2012.

syntax

THROW [ { error_number | @local_variable },

        { message | @local_variable },

        { state | @local_variable } ] 

[ ; ]

error_number 

Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

message

Is an string or variable that describes the exception. message is nvarchar(2048).

state

Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be ended.

Diversities between Raise Error and Throw Statement 

RAISERROR statement	THROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.	The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.	The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.	There is no severity parameter. The exception severity is always set to 16