USP_RAISE_ERROR

CREATE procedure dbo.USP_RAISE_ERROR
(
  @ERRORINSTANCE int = 0
)
as
  set nocount on;

  declare @ERRORMESSAGE nvarchar(1000);
  declare @ERRORNUMBER int;
  declare @ERRORSEVERITY int;
  declare @ERRORSTATE int;
  declare @ERRORLINE int;
  declare @ERRORPROCEDURE nvarchar(400) = '';

  -- Assign variables to error-handling functions to capture information for raiserror.

  select
    @ERRORMESSAGE = error_message(),
    @ERRORNUMBER = error_number(),
    @ERRORSEVERITY = error_severity(),
    @ERRORSTATE = error_state(),
    @ERRORLINE = error_line();

  if @ERRORSTATE = 0
    set @ERRORSTATE = 13;

  if @ERRORSEVERITY = 0
    set @ERRORSEVERITY = 13;

  if error_procedure() is not null
    set @ERRORPROCEDURE = rtrim(error_procedure());

  /* Raise non-throwing errors to provide info to the client about the original error message
      Use the System.Data.SQLClient.SQLException.Errors collection to get to these.  These errors
      will not cause a jump to a catch block, but just fill the return buffer with this information.
  */

  declare @INFOMSG nvarchar(100);

  if @ERRORNUMBER < 50000 --This is a built-in SQL error (FK violation, etc.)

  begin
    --Raise additional errors to provide original error codes back to client.

    set @INFOMSG = 'BBERR_ORIGINAL_ERROR:' + cast(@ERRORNUMBER as nvarchar(10));
    raiserror(@INFOMSG, 1, 11);
  end

  if @ERRORPROCEDURE <> 'USP_RAISE_ERROR' --This was raised explicitly by a raiserror statement

  begin
    set @INFOMSG = 'BBERR_ORIGINAL_PROCEDURE:' + @ERRORPROCEDURE;
    raiserror(@INFOMSG, 2, 22);

    set @INFOMSG = 'BBERR_ORIGINAL_LINE_NUMBER:' + cast(@ERRORLINE as nvarchar(10));
    raiserror(@INFOMSG, 3, 33);
  end

  if(coalesce(@ERRORINSTANCE, 0) <> 0)
  begin
    set @INFOMSG = 'BBERR_ORIGINAL_INSTANCE:' + cast(@ERRORINSTANCE as nvarchar(10));
    raiserror(@INFOMSG, 4, 44);
  end

  --Now raise real error.  This will be caught by any catch blocks or the client.

  raiserror('%s', @ERRORSEVERITY, @ERRORSTATE, @ERRORMESSAGE);

  return 0;