SQL Server uses exception handling to debug stored procedures)

Exception Handling is generally considered the weakest aspect of T-SQL scripting. Fortunately, this was changed in SQL Server 2005 because SQL Server 2005 supports structured exception handling. This article first focuses on the new feature "TRY ...... CATCH ", and then look at some T-SQL examples in SQL Server 2000 and SQL Server 2005, which use the transaction code to deliberately create some violations of constraints. This topic will be further explored in future articles.

In versions earlier than SQL Server, you need to check the global variable "@ error" immediately after executing INSERT, UPDATE, and DELETE to handle exceptions, if the "@ error" variable is not zero (indicating an error), perform some corrective actions. Developers often repeat this code that is not related to the business logic, which leads to repeated code blocks and must be used in combination with the GOTO statement and RETURN statement.

Structured exception handling provides a powerful processing mechanism for controlling complex programs with many dynamic runtime characteristics. At present, this mechanism has proved to be good, and many popular programming languages (such as Microsoft's Visual Basic. Net and Visual C #) support this exception handling mechanism. Next, you will see in the example that using this robust method will make your code more readable and maintainable. The TRY block contains transactional code that may fail, and the CATCH block contains the code executed when an error occurs in the TRY block. If any error occurs in the TRY block, the execution process is transferred to the CATCH block, and the error can be processed. The error function can be used to provide detailed error information. TRY ...... The basic CATCH syntax is as follows:

BEGIN TRY
RAISERROR ('houston, we have a problem', 16, 1)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER () as ERROR_NUMBER,
ERROR_SEVERITY () as ERROR_SEVERITY,
ERROR_STATE () as ERROR_STATE,
ERROR_MESSAGE () as ERROR_MESSAGE
END CATCH

Note the usage of functions in the above script. We can use them to replace local variables and (or) global variables. These functions should only be used in CATCH blocks. The functions are described as follows:

ERROR_NUMBER () returns the number of errors.

ERROR_SEVERITY () returns the error severity level.

ERROR_STATE () returns the error Status Number.

ERROR_PROCEDURE () returns the name of the stored procedure or trigger in the error location.

ERROR_LINE () returns the row number that causes an error in the program.

ERROR_MESSAGE () returns the complete text of the error message. The error content includes the value of replaceable parameters, such as length, object name, or time.

I will first demonstrate a simple example with SQL Server 2000 and then an example of SQL Server 2005 exception handling.

The following is a simple Stored Procedure example, which is first written in SQL Server 2000 and then implemented in SQL Server 2005. Both of them start from simple tables. When we insert these tables, the constraints are violated. The following table structure:

Create table dbo. Titles
(TitleID int Primary Key identity,
TitleName nvarchar (128) not null,
Price money NULL constraint CHK_Price check (Price> 0 ))
Create table dbo. Authors
(Authors_ID int primary key identity,
Au_fname nvarchar (32) NULL,
Au_lname nvarchar (64) NULL,
TitleID int constraint FK_TitleID foreign key
References Titles (TitleID ),
CommissionRating int constraint CHK_ValidateCommissionRating
Check (CommissionRating between 0 and 100 ))
Create table dbo. Application_Error_Log
(Tablename sysname,
UserName sysname,
ErrorNumber int,
ErrorSeverity int,
ErrorState int,
ErrorMessage varchar (4000 ))

Source Code of the stored procedure P_Insert_New_BookTitle_2K
----------------------------------------------

P_Insert_New_BookTitle_2K

----------------------------------------------

Create proc P_Insert_New_BookTitle_2K
(@ TitleName nvarchar (128 ),
@ Price money,
@ Au_fname nvarchar (32 ),
@ Au_name nvarchar (64 ),
@ CommissionRating int)
As

Declare @ err int,
@ Tablename sysname

Begin transaction

Insert dbo. Titles (TitleName, Price)
Values (@ TitleName, @ Price)

Select @ err =@@ error
If @ err <> 0
Begin
Select @ tablename = 'tidles'
GOTO ERROR_HANDLER
End

Insert dbo. Authors (au_fname, au_lname, TitleID, CommissionRating)
Values (@ au_fname, @ au_fname, @ IDENTITY, @ CommissionRating)

If @ err <> 0
Begin
Select @ tablename = 'authhors'
GOTO ERROR_HANDLER
End

GOTO EXIT_Proc


ERROR_HANDLER:

ROLLBACK TRANSACTION

-- Log the error
Insert dbo. Application_Error_Log (tableName, UserName, errorNumber, errorSeverity, errorState)
Values (@ tableName, suser_sname (), @ err, 0, 0)

EXIT_Proc:

Commit tran


----------------------------------------------

As you can see, this Stored Procedure contains unstructured error handling code, which we used before SQL Server 2005.

We have read the code used in the Stored Procedure P_Insert_New_BookTitle_2K. You can say at most: "At least we have an exception ." The following statement executes the stored procedure under SQL Server 2000.

Code
Exec P_Insert_New_BookTitle_2K 'Red Storm rising', 16.99,
'Tom ', 'clance', 200

When the stored procedure is executed with the specified parameter, insertion of the Authors table fails because the commission rate value is invalid. The invalid value is found in our constraints check. We can see the following error message: Code
Msg 547, Level 16, State 0, Procedure P_Insert_New_BookTitle, Line 23 The INSERT statement conflicted with the CHECK constraint "CHK_ValidateCommissionRating ". the conflict occurred in database "Adventureworks2005", table "dbo. authors ", column 'commissionrating '. the statement has been terminated.

The problem here is that we cannot prevent these messages from being sent to the client. Therefore, the burden of judging errors is placed on the client. Unfortunately, in some cases, such results may be sufficient for applications that do not use constraints.

Let's TRY again. This time we use TRY ...... CATCH code block.

Stored Procedure P_Insert_New_BookTitle_2K5 source Code
----------------------------------------------

----------------------------------------------P_Insert_New_BookTitle_2K5 ----------------------------------------------create proc P_Insert_New_BookTitle_2K5(@TitleName nvarchar(128), @Price money, @au_fname nvarchar(32), @au_name nvarchar(64), @CommissionRating int)asdeclare @err int,@tablename sysname,@errormessage nvarchar(2000)BEGIN TRYbegin transactionselect @errormessage = 'insert into Titles table failed', @tablename = 'Titles'insert dbo.Titles (TitleName, Price)values (@TitleName, @Price)select @errormessage = 'insert into Authors table failed', @tablename = 'Authors'insert dbo.Authors  (au_fname, au_lname, TitleID,                      CommissionRating)values (@au_fname, @au_fname, @@IDENTITY,         @CommissionRating)commit transactionEND TRYBEGIN CATCHROLLBACK TRANSACTION-- Log the error insert dbo.Application_Error_Log (UserName, tableName,                 errorNumber, errorSeverity, errorState, errorMessage)values (suser_sname(), @tableName, ERROR_NUMBER(),              ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())  RAISERROR (@errormessage, 16,1)END CATCH----------------------------------------------

In this new improved storage process, we can see the use of TRY ...... Structured error handling of CATCH code blocks:

Note that the SQL Server 2005 Exception Handling Code is simplified, so it has better readability and maintainability. You do not need to cut or paste the exception handling code or use the GOTO statement. When executing the stored procedure, you can see the following results:

Exec P_Insert_New_BookTitle_2K5 'Red Storm rising', 16.99,
'Tom ', 'clance', 200
We use the specified parameter to execute the stored procedure. Likewise, the insertion of the Authors table fails because the commission rate value is invalid. When an error occurs, the Program Execution Process jumps to the CATCH code block. In the CATCH code block, we roll back the transaction, and then insert a line of log to the Application_Error_Log table using the function provided by SQL Server 2005.

New TRY ...... The CATCH code block makes it easier to write and process error codes. It can also prevent error messages from being sent to the client at any time. Of course this may require a shift in programming thinking for T-SQL programmers, which is an absolutely necessary feature. Remember that when migrating SQL Server 2000 Code to SQL Server 2005, if the program's error handling mechanism has been designed as the old method of sending errors to the client, you may have to modify the application. In the long run, I believe it is worthwhile to make efforts to redesign this potential problem.

Note:
Http://www.searchdatabase.com.cn/ShowContent_23265.htm