THROW defaults

• Hany Helmy

  SSChampion

  Points: 13921

  More actions

  October 9, 2014 at 4:05 am

  #1751193

  TomThomson (10/6/2014)

  ---

  Nice question. But the answer turned up in comments on last weeks raiserror question, so it was a bit easy for anyone who had read that.

  I didn`t read that, yet managed to answer the question correctly cause it was in my mind, had read it somwhere sometime

  Thanks & Best Regards,
  Hany Helmy
  SQL Server Database Consultant

• Hany Helmy

  SSChampion

  Points: 13921

  More actions

  October 9, 2014 at 4:06 am

  #1751194

  Another good question and informative as well, thanx.

  Thanks & Best Regards,
  Hany Helmy
  SQL Server Database Consultant

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  October 9, 2014 at 11:31 pm

  #1751487

  Jim_K (10/6/2014)

  ---

  The raiserror documentation says to prefer throw, but it's less capable.

  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

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Andre Ranieri

  SSCrazy

  Points: 2819

  More actions

  January 30, 2015 at 2:29 pm

  #1774481

  +1 great question. Thanks.