Try Catch Block

Hi,

I would probably use XACT ABORT like below

BEGIN CATCH
  IF XACT_STATE <>0
  BEGIN
     ROLLBACK TRANSACTION
  END

DECLARE @ErrorMsg varchar(2100);

SET  @ErrorMsg =  ' DBName        = ' + DB_NAME() + CHAR(10)
+ ' ErrorMessage  = ' + LTRIM(CONVERT(VARCHAR(2047), LEFT(Error_Message(),2044))) + CHAR(10) 
+ ' Procedure     = ' + OBJECT_NAME(@@PROCID) + CHAR(10) + ' ErrorNumber   = ' + 
LTRIM(CONVERT(VARCHAR(9),Error_Number()))  + CHAR(10) 
+ ' ErrorState    = ' + LTRIM(CONVERT(VARCHAR(3),Error_State())) + CHAR(10)
+ ' ErrorSeverity = ' + LTRIM(CONVERT(VARCHAR(3),Error_Severity()))  + CHAR(10)
+ ' LineNumber    = ' + LTRIM(CONVERT(VARCHAR(9),Error_Line()))  + CHAR(10)
+ ' ErrorDT       = ' + CONVERT(VARCHAR(23),GETDATE(),121)   + CHAR(10) + ' ErrorBy       = ' + SUSER_SNAME();


RAISERROR(@ErrorMsg,16,1);
    RETURN -1
END CATCH

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it



My Technet Wiki Article

MVP