New T-SQL Features in SQL Server 2012

THROW in Error Handling

Error handling is now easier with the introduction of the THROW command in SQL Server 2011.

Legacy method (SQL 2005 onwards)

In previous versions, RAISERROR was used to show an error message. RAISERROR requires a proper message number to be shown when raising any error. The message number should exist in sys.messages. RAISERROR cannot be used to re-throw an exception raised in a TRY..CATCH block.

2011 method

Unlike RAISERROR, THROW does not require that an error number to exist in sys.messages (although it has to be between 50000 and 2147483647). All exceptions being raised by THROW have a severity of 16.

You can throw an error using Throw as below:

THROW 50001, ‘Error message’, 1;

This will return an error message:

Msg 50001, Level 16, State 1, Line 1 Error message

THROW even allows for re-throwing an exception caught in a TRY..CATCH block, which RAISERROR was not able to handle:

BEGIN TRY

  SELECT ‘Using Throw’

  SELECT 1 / 0 

END TRY

BEGIN CATCH

  –Throw error

  THROW

END CATCH

The above code snippet produces this output:

(1 row(s) affected)

 (0 row(s) affected)

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

THROW has now made the developer’s life much easier, and developers can now code independent of the Tester’s input on the exception message.

NOTE: The current version of 2011 Books Online indicates that RAISERROR has been deprecated. Due to this, the use of RAISERROR should be minimized.

 

Continues…

Pages: 1 2 3 4




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |