In SQL Server 2005 we got the TRY…CATCH construct which was a big help for developers to effectively handle errors within their T-SQL code. Prior to that the developers had to use the @@ERROR in-built system function to check for errors and they had to check for error conditions after every operation! That sucked big and too often the developers forgot.
- It require an error number to exist within the sys.message.
- The severity level controls the error actions, such as dtatement abort.
- RAISERROR does not honors XACT_ABORT.
- The error number, message, line number could get changed when using RAISERROR.
- ...
And even if you could go around most of the issues, example by embed the original error details as a custom message passed to RAISERROR, it was always hard to know what happened just by reading the T-SQL since you had to know the parameters, like ErrorSeverity.
- As an alternative to RAISERROR.
- As an away to re-throw the original error that occurred.
SQL Server 2008
SET NOCOUNT ON
SELECT 1/0
END TRY
BEGIN CATCH
DECLARE @ErrorNumber int
DECLARE @ErrorState int
DECLARE @ErrorSeverity int
DECLARE @ErrorLine int
DECLARE @ErrorProcedure NVARCHAR(MAX)
DECLARE @ErrorMessage NVARCHAR(MAX)
DECLARE @UserName NVARCHAR(256)
DECLARE @HostName NVARCHAR(128)
,@ErrorState = ERROR_STATE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@UserName = SUSER_SNAME()
,@HostName = Host_NAME()
RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )
END CATCH
Divide by zero error encountered.
SELECT 1/0;
END TRY
BEGIN CATCH
THROW;
END CATCH
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
