PRINT ' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) If you try to capture error information like error number and message, you get only the last: BEGIN TRYīACKUP DATABASE master TO DISK = 'c:\Nonexistent Folder\master.bak' Operating system error 3(The system cannot find the path specified.).īACKUP DATABASE is terminating abnormally. Consider the following error for example due to a backup target that doesn’t exist: BACKUP DATABASE master TO DISK = 'c:\Nonexistent Folder\master.bak' Ĭannot open backup device 'c:\Nonexistent Folder\master.bak'. In this example I wrote code that handles errors 8134 (divide by zero) and 8115 (arithmetic overflow), but I let the upper level deal with the rest of the errors (e.g., 8114, which is a conversion error).Īnother important scenario that the new THROW covers is cases where a single error causes multiple error messages to be generated. Handling Arithmetic overflow error converting expression to data type tinyint.Įrror converting data type varchar to numeric. PRINT ' Let upper level deal with error.'
PRINT ' Handling Arithmetic overflow error converting expression to data type tinyint.' e.g., deal with some errors in current level, and let upper level deal with rest Now you simply invoke THROW in the ELSE block of your error handling logic. In the past you had to raise a user-defined error for the caller to deal with, making it very awkward to pass and interpret the error information-it wasn’t the original error. One scenario is when you want to deal with a certain set of errors in one CATCH block, but let the upper level in the call stack deal with the rest. Also, whether you execute it with or without parameters, the statement before THROW must be terminated with a semicolon-one more reason to try and get to the habit of simply terminating all of your statements with a semicolon.Īt any rate, I wanted to touch on a couple of important scenarios that I struggled with in the past, and that are now addressed elegantly with the new parameterless THROW. First, you need to be aware that executing THROW without parameters is only allowed within a CATCH block. With parameters to throw a user defined error. This will essentially re-throw the original error.
Without any parameters within the CATCH block of a TRY/CATCH construct. The THROW command can be invoked in two main ways: SQL Server 2012 (formerly code-named Denali) addresses important needs that were not covered so far by introducing the THROW command. SQL Server made a big leap with its error handling support in SQL Server 2005 where the TRY/CATCH construct was initially introduced but then SQL Server 2008 didn’t really add any new error handling features.
0 kommentar(er)
