MS SQL 2011 - Error Handling
A new useful addition for SQL Server 2011 (Denali) is Throw expression. The developers on .Net probably already guessed where and how it will be used.
This word can be used in conjunction with the Try ... Catch control structure and allows you to send a notification about the occurrence of a runtime error. When an exception occurs, the program looks for the nearest Catch block that can handle the exception. Using this expression inside the Catch block, you can change the error output. Moreover, now it is possible to raise an exception arbitrarily anywhere in the script.
Next, consider the various ways of catching the exceptions that SQL Server provides from version 2000 to version 2011, with an indication of the pros and cons.
')
For all cases considered, the tbl_ExceptionTest table will be used.
In order not to pierce the designer with the mouse, you can run the following script to create the desired table (generated automatically).
Next, we will try to add several entries to the table and generate exceptions when entering inappropriate data in the Phone Number column.
Returning at the time of using SQL Server 2000, we recall that using the @@ Error variable was at that time the most progressive and efficient way to handle errors. This variable was responsible for returning the integer value of the error that occurred in the last expression executed. The error value could be both positive and negative, only 0 indicated the success of the operation. The value of the variable changed after each expression executed.
Let's look at using @@ Error in action.
The general meaning of the script is to ensure that in the last entry we intentionally cause an error and read its value from a local variable. If the error value is not zero, then we show a sensible warning to the user. If there are no errors, then save the results.
Running this script will result in an error, as shown below.
Msg 515, level 16, state 2, line 26 column does not allow nulls. INSERT fails. The statement has been terminated. Msg 50000, Level 16, State 1, Line 43 Attempt to insert null value in [Phone Number] is not allowed
Naturally, the entire transaction will be rolled back and nothing will be entered into the table.
If you want to know more details and nuances of using @@ Error, then I advise you to refer to the article about @@ Error .
This variable returns the number of transactions executed at the time the variable was accessed. From the description it is already clear that it is approximately the same as @@ ERROR, i.e. constantly changing during the execution of transactions. This again leads us to use local variables to store values at the point in time.
Each BEGIN TRANSACTION call increases the @@ TRANCOUNT value by 1 and each COMMIT TRANSACTION call decreases its value by 1. ROLLBACK TRANSACTION does not change the @@ TRANCOUNT value. Entries are considered to be made only when the @@ TRANCOUNT value reaches 0.
Consider using @@ TRANCOUNT in the following example.
In this script, we rely on the number of closed transactions. Transactions can be nested, so this method has the right to exist.
For more information on @@ TRANCOUNT, refer to MSDN.
This variable returns the number of rows changed as a result of the query / command.
The behavior is the same as the previous two, so we save the intermediate results in a local variable for later analysis.
Example:
In this case, we expect one record to be inserted into the table, but if the number of records inserted is zero, then obviously something is wrong.
For more details on using @@ ROWCOUNT read MSDN.
After the introduction of SQL Server 2005 to the market and the development of its ideas in SQL Server 2008, TSql developers got a new Try ... Catch block. Now it is possible to catch exceptions without losing the transactional context.
An example of using the Try ... Catch block.
The example no longer uses auxiliary variables to determine the script execution error based on indirect signs.
After starting the script, we will receive a message of the following form:
Msg 50000, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed
As you may have noticed, this time only what was specified in the error message was displayed. No additional, embarrassing user messages, SQL Server showed. Executable code is framed in a try block and error handling in a catch block. The code turns out pure and clear for understanding. If all the desired code has passed without errors, the code from the Catch block will not be called.
The most important thing is that the Catch block represents a set of functions for a detailed analysis of the causes of the error and the ability to inform the user at the proper level. Functions to parse an exception:
Using these functions, we will try to rewrite the Catch script block, which would be presented before.
Now we get the following response from the server:
1 If we recall what this function caused in the Catch block showed, then we note that it referred to line number 45 as the source of the problems.
However, in reality, the error occurred in line number 24, where it was written
Insert into #tblExceptionTest ([Phone Number]) Values (null)
While the ERROR_LINE () function always returns the actual location of the error. Another way to show how new features work is:
In this case, the SQL Server engine will display the following message:
From which we can conclude that the use of RaiseError makes it impossible to point to the real place in the script where the exception occurred.
2 The next drawback of the RaiseError function is that it is not possible to re-trigger the same exception itself, to pass up the call hierarchy. So, if you rewrite the Catch block as shown below
That error message received will be:
Msg 2732, Level 16, State 1, Line 46 Error number 515 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000
The reason for this is that in order to initiate a new error message, the error number must be contained in the sys table . messages .
For a more detailed study of the function RaiseError, it is recommended to read:
The above disadvantages of the RaiseError function can be successfully overcome with the new Throw command .
The first drawback of the RaiseError function, which we pointed out earlier, is the inability to refer to the exact error string. Consider how far from the location of the error we find ourselves when using the Throw command.
Rewrite the Catch block using the Throw command.
The output will be:
This is exactly where the error occurred. Well, it works perfectly well for now.
The second disadvantage was that the RaiseError function could not re-trigger the exception because RAISE ERROR expects an error number, which is stored in the sys.messages table. The Throw command does not expect that the error number should be from the sys.messages system table range, but the number can be set from the range from 50,000 to 2,147,483,647 inclusive.
Again we will change the Catch block according to new knowledge.
The result of the exception will be
Msg 50001, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed
Currently, SQL Server provides many ways to catch errors, but so far not all errors can be caught using the Try ... Catch block. For example:
If you try to submit for execution the following script:
We will receive the following error message:
Msg 208, Level 16, State 0, Line 3 Invalid object name 'tblInvalid'.
It turns out that it is almost impossible to intercept these types of errors.
But. As always, there is a little trick to do what you want. The basic idea is to make two stored procedures and call one of the other in the Try ... Catch block and catch an exception. To prove our assumption, we use the following script for experiments.
When you run the ExternalStoredProc procedure, we get the message:
And the Result panel will display the following data:
What we needed!
Now a little explanation of how the code works. We have 2 stored procedures: usp_InternalStoredProc and usp_ExternalStoredProc . In usp_InternalStoredProc, we are trying to insert a record into the nonexistent table #tblInnerTempTable, as a result of which we get an exceptional situation, which in turn is caught by an external Catch block located in an external procedure.
Moreover, the line and the text of the error fully meet our expectations and indicate the exact place.
It is very important not to forget to close with a semicolon the expression before the THROW statement in the external procedure. THROW must be a new command set. Otherwise, get an error
Incorrect syntax near 'THROW'.
More detailed information about THROW can be found in MSDN.
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
This word can be used in conjunction with the Try ... Catch control structure and allows you to send a notification about the occurrence of a runtime error. When an exception occurs, the program looks for the nearest Catch block that can handle the exception. Using this expression inside the Catch block, you can change the error output. Moreover, now it is possible to raise an exception arbitrarily anywhere in the script.
Next, consider the various ways of catching the exceptions that SQL Server provides from version 2000 to version 2011, with an indication of the pros and cons.
')
For all cases considered, the tbl_ExceptionTest table will be used.
In order not to pierce the designer with the mouse, you can run the following script to create the desired table (generated automatically).
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'tbl_ExceptionTest' AND type = 'U') DROP TABLE tbl_ExceptionTest GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tbl_ExceptionTest]( [Id] [int] IDENTITY(1,1) NOT NULL, [Phone Number] [int] NOT NULL, CONSTRAINT [PK_tbl_ExceptionTest] PRIMARY KEY CLUSTERED ) Next, we will try to add several entries to the table and generate exceptions when entering inappropriate data in the Phone Number column.
Error Handling in SQL Server 2000 (Sphinx)
Using the global variable @@ ERROR
Returning at the time of using SQL Server 2000, we recall that using the @@ Error variable was at that time the most progressive and efficient way to handle errors. This variable was responsible for returning the integer value of the error that occurred in the last expression executed. The error value could be both positive and negative, only 0 indicated the success of the operation. The value of the variable changed after each expression executed.
Let's look at using @@ Error in action.
-- #tblExceptionTest , . If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End -- #tblExceptionTest Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) -- Begin Transaction TranExcp__2000_@@Error -- -- @@ERROR Declare @ErrorNum int -- Declare @i int -- Set @i =1 -- While(@i <= 4) Begin -- null Phone Number If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) Set @ErrorNum = @@ERROR End Else -- Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End -- while -- , If @ErrorNum <> 0 Begin Rollback Transaction TranExcp__2000_@@Error -- RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End -- Else If @ErrorNum = 0 Begin Commit Transaction TranExcp__2000_@@Error End -- Select * from #tblExceptionTest The general meaning of the script is to ensure that in the last entry we intentionally cause an error and read its value from a local variable. If the error value is not zero, then we show a sensible warning to the user. If there are no errors, then save the results.
Running this script will result in an error, as shown below.
Msg 515, level 16, state 2, line 26 column does not allow nulls. INSERT fails. The statement has been terminated. Msg 50000, Level 16, State 1, Line 43 Attempt to insert null value in [Phone Number] is not allowed
Naturally, the entire transaction will be rolled back and nothing will be entered into the table.
Disadvantages of the @@ Error approach
- The value of the @@ Error variable should be checked immediately after the execution of the request / command.
- Since @@ Error is constantly changing, we are forced to set up a separate variable to save and display the error code.
- Together with a special error message pointing to the logical meaning of the error, technical information is displayed that is not interesting to users.
If you want to know more details and nuances of using @@ Error, then I advise you to refer to the article about @@ Error .
Using the global @@ TRANCOUNT variable
This variable returns the number of transactions executed at the time the variable was accessed. From the description it is already clear that it is approximately the same as @@ ERROR, i.e. constantly changing during the execution of transactions. This again leads us to use local variables to store values at the point in time.
Each BEGIN TRANSACTION call increases the @@ TRANCOUNT value by 1 and each COMMIT TRANSACTION call decreases its value by 1. ROLLBACK TRANSACTION does not change the @@ TRANCOUNT value. Entries are considered to be made only when the @@ TRANCOUNT value reaches 0.
Consider using @@ TRANCOUNT in the following example.
-- #tblExceptionTest , If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End -- Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) -- Begin Transaction TranExcp__2000_@@TRANCOUNT -- -- @@TRANCOUNT Declare @TransactionCount int -- Declare @i int -- Set @i =1 -- While(@i <= 4) Begin -- null Phone Number If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) Set @TransactionCount = @@TRANCOUNT End Else -- Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End -- while -- , If @TransactionCount <> 0 Begin Rollback Transaction TranExcp__2000_@@TRANCOUNT -- RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End -- Else If @TransactionCount = 0 Begin Commit Transaction TranExcp__2000_@@TRANCOUNT End -- Select * from #tblExceptionTest In this script, we rely on the number of closed transactions. Transactions can be nested, so this method has the right to exist.
For more information on @@ TRANCOUNT, refer to MSDN.
Using the global variable @@ ROWCOUNT
This variable returns the number of rows changed as a result of the query / command.
The behavior is the same as the previous two, so we save the intermediate results in a local variable for later analysis.
Example:
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) Begin Transaction TranExcp__2000_@@ROWCOUNT Save Transaction TranExcp__SavePoint Declare @RowCount int Declare @i int Set @i =1 While(@i <= 4) Begin If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) Set @RowCount = @@ROWCOUNT End Else Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End If @RowCount = 0 Begin Rollback Transaction TranExcp__SavePoint RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End Else If @RowCount <> 0 Begin Commit Transaction TranExcp__2000_@@ROWCOUNT End Select * from #tblExceptionTest In this case, we expect one record to be inserted into the table, but if the number of records inserted is zero, then obviously something is wrong.
For more details on using @@ ROWCOUNT read MSDN.
Error Handling in SQL Server 2005/2008 (Yukon / Katmai)
After the introduction of SQL Server 2005 to the market and the development of its ideas in SQL Server 2008, TSql developers got a new Try ... Catch block. Now it is possible to catch exceptions without losing the transactional context.
An example of using the Try ... Catch block.
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End Begin TRY Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) Begin Transaction TranExcpHandlingTest_2005_2008 Declare @i int Set @i =1 While(@i <= 4) Begin If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) End Else Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End Commit Transaction TranExcpHandlingTest_2005_2008 End Try Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2005_2008 RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End End Catch Select * From #tblExceptionTest The example no longer uses auxiliary variables to determine the script execution error based on indirect signs.
After starting the script, we will receive a message of the following form:
Msg 50000, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed
As you may have noticed, this time only what was specified in the error message was displayed. No additional, embarrassing user messages, SQL Server showed. Executable code is framed in a try block and error handling in a catch block. The code turns out pure and clear for understanding. If all the desired code has passed without errors, the code from the Catch block will not be called.
The most important thing is that the Catch block represents a set of functions for a detailed analysis of the causes of the error and the ability to inform the user at the proper level. Functions to parse an exception:
- ERROR_NUMBER
- ERROR_SEVERITY
- ERROR_STATE
- ERROR_LINE
- ERROR_PROCEDURE
- ERROR_MESSAGE
Using these functions, we will try to rewrite the Catch script block, which would be presented before.
Begin Catch -- Begin -- Rollback Transaction TranExcpHandlingTest_2005_2008 SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; End End Catch Now we get the following response from the server:
Disadvantages of using the RaiseError function
1 If we recall what this function caused in the Catch block showed, then we note that it referred to line number 45 as the source of the problems.
However, in reality, the error occurred in line number 24, where it was written
Insert into #tblExceptionTest ([Phone Number]) Values (null)
While the ERROR_LINE () function always returns the actual location of the error. Another way to show how new features work is:
Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2005_2008 DECLARE @errNumber INT = ERROR_NUMBER() DECLARE @errMessage VARCHAR(500) = 'Attempt to insert null value in [Phone Number] is not allowed' RAISERROR('Error Number: %d, Message: %s', 16, 1, @errNumber, @errMessage) End End Catch In this case, the SQL Server engine will display the following message:
From which we can conclude that the use of RaiseError makes it impossible to point to the real place in the script where the exception occurred.
2 The next drawback of the RaiseError function is that it is not possible to re-trigger the same exception itself, to pass up the call hierarchy. So, if you rewrite the Catch block as shown below
Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2005_2008 RAISERROR(515, 16, 1) End End Catch That error message received will be:
Msg 2732, Level 16, State 1, Line 46 Error number 515 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000
The reason for this is that in order to initiate a new error message, the error number must be contained in the sys table . messages .
For a more detailed study of the function RaiseError, it is recommended to read:
Error Handling in SQL Server 2011 (Denali)
The above disadvantages of the RaiseError function can be successfully overcome with the new Throw command .
The first drawback of the RaiseError function, which we pointed out earlier, is the inability to refer to the exact error string. Consider how far from the location of the error we find ourselves when using the Throw command.
Rewrite the Catch block using the Throw command.
Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2011; THROW End End Catch The output will be:
This is exactly where the error occurred. Well, it works perfectly well for now.
The second disadvantage was that the RaiseError function could not re-trigger the exception because RAISE ERROR expects an error number, which is stored in the sys.messages table. The Throw command does not expect that the error number should be from the sys.messages system table range, but the number can be set from the range from 50,000 to 2,147,483,647 inclusive.
Again we will change the Catch block according to new knowledge.
Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2011; THROW 50001,'Attempt to insert null value in [Phone Number] is not allowed',1 End End Catch The result of the exception will be
Msg 50001, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed
Currently, SQL Server provides many ways to catch errors, but so far not all errors can be caught using the Try ... Catch block. For example:
- Syntax errors are captured by the query editor in SSMS
- Wrong object names
If you try to submit for execution the following script:
Begin Try -- tblInvalid Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26')) End Try Begin Catch -- THROW End Catch We will receive the following error message:
Msg 208, Level 16, State 0, Line 3 Invalid object name 'tblInvalid'.
It turns out that it is almost impossible to intercept these types of errors.
But. As always, there is a little trick to do what you want. The basic idea is to make two stored procedures and call one of the other in the Try ... Catch block and catch an exception. To prove our assumption, we use the following script for experiments.
-- , , If Exists (Select * from sys.objects where name = 'usp_InternalStoredProc' and type = 'P') Drop Procedure usp_InternalStoredProc Go -- Create Procedure usp_InternalStoredProc As Begin Begin Transaction TranExcpHandlingTest_2011 Begin Try -- Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26')) -- Commit Transaction TranExcpHandlingTest_2011 End Try Begin Catch If @@TRANCOUNT > 0 Rollback Transaction TranExcpHandlingTest_2011 Print 'In catch block of internal stored procedure.... throwing the exception'; -- THROW End Catch End Go -- -- , , If Exists (Select * from sys.objects where name = 'usp_ExternalStoredProc' and type = 'P') Drop Procedure usp_ExternalStoredProc Go -- Create Procedure usp_ExternalStoredProc As Begin Begin Try -- Exec usp_InternalStoredProc End Try Begin Catch Print 'In catch block of external stored procedure.... throwing the exception'; SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; THROW End Catch End Go -- Exec usp_ExternalStoredProc When you run the ExternalStoredProc procedure, we get the message:
In catch block of external stored procedure.... throwing the exception (1 row(s) affected) Msg 208, Level 16, State 1, Procedure usp_InternalStoredProc, Line 8 Invalid object name 'tblInvalid'. And the Result panel will display the following data:
What we needed!
Now a little explanation of how the code works. We have 2 stored procedures: usp_InternalStoredProc and usp_ExternalStoredProc . In usp_InternalStoredProc, we are trying to insert a record into the nonexistent table #tblInnerTempTable, as a result of which we get an exceptional situation, which in turn is caught by an external Catch block located in an external procedure.
Moreover, the line and the text of the error fully meet our expectations and indicate the exact place.
It is very important not to forget to close with a semicolon the expression before the THROW statement in the external procedure. THROW must be a new command set. Otherwise, get an error
Incorrect syntax near 'THROW'.
More detailed information about THROW can be found in MSDN.
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
Source: https://habr.com/ru/post/123507/
All Articles