USP_RAISE_ERROR

create procedure BBDW.[USP_RAISE_ERROR]
as
  --Call this from a catch block to rethrow an error.
  set nocount on;

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

  -- Assign variables to error-handling functions to 
  -- capture information for raiserror.
  select 
    @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 null)
    select @ERRORPROCEDURE = '';
  else
    select @ERRORPROCEDURE = RTRIM(ERROR_PROCEDURE()) ;

  select @ERRORMESSAGE = ERROR_MESSAGE();


  /* 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.    
  */

  if @ERRORNUMBER < 50000 --This is a built-in SQL error (FK violation, etc.)
  begin
    declare @INFOMSG nvarchar(100)
    --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

  /*
   Now raise real error.  This will be caught by any catch blocks or the client.
  */
  raiserror 
  (
    @ERRORMESSAGE, 
    @ERRORSEVERITY, 
    @ERRORSTATE 
  );

  return 0;