begin try catch on sql server 2005, how to send the ERROR_stuff to the calling parent?

Question

you have this procedure

 CREATE PROCEDURE dbo.test1
 AS
 BEGIN
  begin transaction
  begin try
    exec dbo.test2
    commit transaction
  end try
  begin catch
   SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage
     rollback transaction
  end catch
 END

and this one

 CREATE PROCEDURE dbo.test2
 AS
 BEGIN
  begin transaction
  begin try

    commit transaction
  end try
  begin catch
     rollback transaction
            if @@trancount = 0
            begin
    SELECT
     ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage
             end
             else
                --return the select above to the parent while raising an error
  end catch
 END

how to do re-throw the error to the calling parent?

I know I could do this;

  raiserror(ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE())

but how to get ERROR_LINE(), ERROR_PROCEDURE() and ERROR_NUMBER()?

I need to be able to just do the same select in every catch, if possible

Answer 1

use raiserror in your child sproc.

Answer 2

We use a stored proc (was udf, but added exception logging) that we call in every catch block to concatenate what we need and RAISERROR again.

The calling stored proc calls the error handler proc again and add more info etc.

The ERROR_xxx() functions are available in the error handler proc because it's in the catch block scope.

Eventually, the client (c# in our case) gets the exception.