Subscribe to this blog
Briefcase
Print
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.
Developers also had to use the RAISERROR (notice the spelling mistake that we are forced to live with) statement to show an error message. But there where many issues:
- 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.
- ...
In SQL Server 2012, you can use new THROW statement, borrowed from throw in the .NET model) and it can be used in two ways:
- As an alternative to RAISERROR.
- As an away to re-throw the original error that occurred.
Note that THROW() need the preceding statement to end with a proper statement terminator. So start using those semi-colons!
BEGIN TRY
SELECT 1/0END 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)
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorState = ERROR_STATE() ,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@UserName = SUSER_SNAME()
,@HostName = Host_NAME()
SELECT @ErrorNumber,@ErrorState,@ErrorSeverity,@ErrorLine,@ErrorProcedure,@ErrorMessage,@UserName,@HostName
RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )
END CATCH
Msg 50000, Level 16, State 1, Line 26
Divide by zero error encountered.SQL Server 2012
SET NOCOUNT ON;
BEGIN TRY
SELECT 1/0;END TRY
BEGIN CATCH
THROW;
END CATCH
Divide by zero error encountered.
Start lobbying your developer to use the new THROW statement and you will get better error handling and easier problem detection. This equals more time for you to do the fun SQL stuff!
Loading comments...