Throw - SQL Server 2012
Throw is recently introduced in SQL Server 2012. One of the best practices to avoid unexpected behavior of T-SQL code can be accomplished by using Exception Handling. With exception handling, developer can show or prompt system generated or user defined messages in error situations.
@@ERROR was the only option before SQL server 2005 to serve purpose of exception handling. In or after SQL server 2005, RAISEERROR was introduced to show User Defined exception messages. From SQL Server 2005 till SQL Server 2012, it was the only option for producing user defined exceptions.
Throw is recently introduced in SQL Server 2012. Throw statement which resembles .NET’s model, is the other option which can be used in place of RAISEERROR. It can be used in 2 ways:
1) It can be used as an alternative to RAISERROR. For this, the THROW statement requires parameters:
2) The other usage includes use of THROW with no parameters. It can be placed only inside the CATCH block. With this, an unexpected situation occurs in TRY block which triggers and executes the corresponding CATCH block. CATCH block may perform error handling like log errors, roll back transaction, etc. and then write a Throw statement with 0 parameters. It will re-throw actual error that was occurred including more information like code, message, severity and state.
Example of Throw:
In try block we have written error prone statement which will throw divide by zero exception. Once that exception comes, SQL statement will first log an entry in our created table i.e. Exception_Log & then it will re-throw original exception which can be used by application (application is the place where we are referring this T-SQL code).
Throw vs RAISEERROR
1. RAISEERROR was introduced with SQL Server 2005 whereas Throw is a part of SQL Server 2012.
2. Throw will re-throw the exception that actually occurred including its code number, message string, severity and its state whereas RAISERROR always throws a new exception. RAISEERROR only simulates exception by re-throwing the actual exception by capturing exception’s ERROR_MESSAGE() string, ERROR_SEVERITY(), and ERROR_STATE ()inside the CATCH block. With all these values RAISEERROR prompts a new exception.
3. The error messages don’t appear in real but stored in buffer. On the other hand, RAISERROR supports immediate flushing of buffer by giving WITH NOWAIT option.
Throw is recently introduced in SQL Server 2012. One of the best practices to avoid unexpected behavior of T-SQL code can be accomplished by using Exception Handling. With exception handling, developer can show or prompt system generated or user defined messages in error situations.
@@ERROR was the only option before SQL server 2005 to serve purpose of exception handling. In or after SQL server 2005, RAISEERROR was introduced to show User Defined exception messages. From SQL Server 2005 till SQL Server 2012, it was the only option for producing user defined exceptions.
Throw is recently introduced in SQL Server 2012. Throw statement which resembles .NET’s model, is the other option which can be used in place of RAISEERROR. It can be used in 2 ways:
1) It can be used as an alternative to RAISERROR. For this, the THROW statement requires parameters:
a. error code,
b. description and
c. state.
2) The other usage includes use of THROW with no parameters. It can be placed only inside the CATCH block. With this, an unexpected situation occurs in TRY block which triggers and executes the corresponding CATCH block. CATCH block may perform error handling like log errors, roll back transaction, etc. and then write a Throw statement with 0 parameters. It will re-throw actual error that was occurred including more information like code, message, severity and state.
Example of Throw:
CREATE TABLE Exception_Log ( Exception_Msg VARCHAR(1000), Time_Of_Occurrence DATETIME, Exception_Severity VARCHAR(500) ) BEGIN TRY DECLARE @Result INTEGER = 10 / 0; END TRY BEGIN CATCH INSERT INTO Exception_Log (Exception_Msg,Time_Of_Occurrence,Exception_Severity) VALUES (ERROR_MESSAGE(), SYSDATETIME(), ERROR_SEVERITY()); -- Logging Exception THROW; -- Re-Throw exception END CATCH
In try block we have written error prone statement which will throw divide by zero exception. Once that exception comes, SQL statement will first log an entry in our created table i.e. Exception_Log & then it will re-throw original exception which can be used by application (application is the place where we are referring this T-SQL code).
Throw vs RAISEERROR
1. RAISEERROR was introduced with SQL Server 2005 whereas Throw is a part of SQL Server 2012.
2. Throw will re-throw the exception that actually occurred including its code number, message string, severity and its state whereas RAISERROR always throws a new exception. RAISEERROR only simulates exception by re-throwing the actual exception by capturing exception’s ERROR_MESSAGE() string, ERROR_SEVERITY(), and ERROR_STATE ()inside the CATCH block. With all these values RAISEERROR prompts a new exception.
3. The error messages don’t appear in real but stored in buffer. On the other hand, RAISERROR supports immediate flushing of buffer by giving WITH NOWAIT option.