The major advantage of THROW over RAISERROR is true support for "re-raising" errors, which is aligned with the TRY/CATCH constructs in languages like Java and C#.

Consider the case when your insert statement encounters a primary key violation, an error with id 2627. In your stored procedure this PK error transfers control to your catch block. To signal the original caller that a PK error occurred in SQL 2005-2008 R2 we are compelled to write code similar to this:

BEGIN TRY

-- do some stuff...until you encounter a PK error with error ID 2627

END TRY

BEGIN CATCH

DECLARE @err_str VARCHAR(2048),

@err_sev INT,

@err_state INT ;

SELECT @err_str = ERROR_MESSAGE() + N' (Line ' + ISNULL(CAST(ERROR_LINE() AS NVARCHAR(11)), N'(null)') + N')',

@err_sev = ERROR_SEVERITY(),

@err_state = ERROR_STATE() ;

RAISERROR(@err_str, @err_sev, @err_state) ;

END CATCH

When RAISERROR is executed within the catch block the error message id will be reported to the caller as 50000, the default user-defined error message id, because we are not permitted to raise errors with a message id < 13000.

In SQL 2012 and higher we can write code like this:

BEGIN TRY

-- do some stuff...until you encounter a PK error with error ID 2627

END TRY

BEGIN CATCH

-- much cleaner, more effective exception handling code (in my opinion)

IF ERROR_NUMBER() = 50100

BEGIN

-- user-defined error 50100 encountered

-- perform some custom logging or recovery logic here to handle error 50100

END

-- (re)throw the original error to the caller. in the case of error 2627, a

-- PK error, the caller will see that exact error as if there were no catch

-- block in place which was not possible prior to THROW being added to SQL 2012

THROW;

END CATCH