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




Array

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 |