Tuesday, 11 June 2013

MSSQL - Function to format detailed error message

  1. -- Drop Function if exists
  2. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.fnGetDetailedErrorMessage'))
  3. DROP FUNCTION dbo.fnGetDetailedErrorMessage;
  4.  
  5. GO
  6. /*=============================================
  7. Author:
  8. Yuri Abele
  9. Changes:
  10. 11.06.2013 - Yuri Abele - initial
  11. Description:
  12. Function to format last error as detailed message
  13.  
  14. Usage: Log last error
  15. BEGIN TRY
  16. DECLARE @i INT;
  17. PRINT 'Try division by zero'
  18. SET @i = 1/0;
  19. PRINT 'After'
  20. END TRY
  21. BEGIN CATCH
  22. -- Display error
  23. PRINT dbo.fnGetDetailedErrorMessage();
  24. IF @@TRANCOUNT > 0 ROLLBACK;
  25. END CATCH;
  26. =============================================*/
  27. CREATE FUNCTION dbo.fnGetDetailedErrorMessage()
  28. RETURNS NVARCHAR(MAX)
  29. AS
  30. BEGIN
  31. RETURN ('ERROR:
  32. ErrorNumber = ' + CAST(ERROR_NUMBER() AS VARCHAR(20)) + ',
  33. ErrorSeverity = ' + CAST(ERROR_SEVERITY() AS VARCHAR(20)) + ',
  34. ErrorState = ' + CAST(ERROR_STATE() AS VARCHAR(20)) + ',
  35. ErrorProcedure = "' + ISNULL(ERROR_PROCEDURE(), '') + ',
  36. ErrorLine = ' + CAST(ERROR_LINE() AS VARCHAR(20)) + ',
  37. ErrorMessage = "' + ERROR_MESSAGE() + '"' + CHAR(13) + CHAR(10));
  38. END;
  39. GO
  40.  
  41. BEGIN TRY
  42. DECLARE @i INT;
  43. PRINT 'Try division by zero'
  44. SET @i = 1/0;
  45. PRINT 'After'
  46. END TRY
  47. BEGIN CATCH
  48. -- Display error
  49. PRINT dbo.fnGetDetailedErrorMessage();
  50. IF @@TRANCOUNT > 0 ROLLBACK;
  51. END CATCH;

Results of execution:

No comments:

Post a Comment

Note: only a member of this blog may post a comment.