Chapter 6: Error Handling


A developer's effective use of error handling procedures is often an excellent indicator of his or her experience in that particular programming language. Those of us who deal with a C# or Visual Basic environment are accustomed to a whole set of feature-rich error handling objects, procedures, and functions. Compared with those, Transact-SQL seems rather inadequate. However, the situation has improved a little since earlier versions. Remember, as a professional, you cannot let the apparent limitations of this tool set justify sloppy solutions.

This section starts by investigating how errors can be returned to a caller. It then discusses the basic concept and statements from the classic @@ Error function, through the new Try-Catch block, to an unorthodox solution based on the Set Xact_Abort On statement. The chapter also includes three sections with three coherent error handling architectures/methodologies that you can use on your projects.

Raiserror()

An important tool for implementing error handling is the Raiserror statement. Its main purpose is to return a user-defined or system-defined message to the caller. Open a Query window and execute the following statement:

      Raiserror ('An error occurred!', 0, 1) 

The second and third parameters indicate the severity and state of the error. Management Studio will display an error message in the Results pane (see Figure 6-1).

image from book
Figure 6-1: Using Raiserror

Seventy provides a rough indication of the type of problem that SQL Server has encountered. Acceptable values are 0 to 25; however, ordinary users and applications can specify only 0 to 18. Only members of the sysadmin role or users with Alter Trace permissions are allowed to set errors above 18. Severity levels 20 to 25 are considered catastrophic. SQL Server will stop executing Transact-SQL code if such an error is encountered. You should typically use severity levels 11 to 16. Severity level 10 indicates issues with values entered by the caller. Severity level 10 and below are considered warnings. If such an error is raised, the engine will display the error message as ordinary text and continue as if nothing has happened. You will see later that it will not even be caught by the Try-Catch statement. Severity levels between 11 and 16 are also used for errors that can be corrected by the user. If they occur, Management Studio will display the error as red text. Errors with a severity of 17 or higher are not considered application errors and should be reported to an administrator.

The state of the error is used when the same error is raised from multiple positions in the code. By specifying the state of the error, you will be able to distinguish between these multiple instances.

After the error is raised, the caller will be able to detect it. If the caller is written in Transact-SQL, it can, for example, investigate the value of the @@Error function. If the Raiserror statement does not specify an error number (as was the case in the preceding example), SQL Server assigns the default value of 50000 to the error.

You can also display errors that are predefined in SQL Server if you reference them by their numbers, and you can define your own errors using the sp_addmessage system stored procedure:

      Exec sp_addmessage 50001,                         16,                         'Unable to update Total of LeaseSchedule' 

Then you can display this message using the following statement:

      Raiserror (50001, 16, 1} 

The server will return the following:

      Server: Msg 50001, Level 16, State 1, Line 1\      Unable to update Total of LeaseSchedule 
Note 

Error numbers below 50000 are reserved for SQL Server errors. You can use any integer above 50000 for your errors.

You can set the state and severity of the error, record the error in the SQL Server Error Log, and even record the error in the Windows NT Error Log:

      Raiserror (50001, 16, 1} WITH LOG 
Note 

SQL Server requires that all errors witb a severity of 19 or higher be logged.

Management Studio contains a tool for displaying and editing error messages. To start it, click a server node and then choose Tools | Manage SQL Server Messages.

It is also possible to define messages (using sp_addmessage) that will accept additional arguments at runtime (using Raiserror):

      Exec sp_addmessage 50002,                         16,                         'Unable to update %s. ' 

%s is a placeholder for string arguments and %d is one for integers. The syntax is inspired by the printf statement in C. You can find more information on this topic in SQL Server Books OnLine, but you will typically use just these two placeholders.

The Raiserror command can accept up to 20 arguments (values or local variables) at the end of the parameter list.

      Raiserror (50002, 16, 1, 'LeaseSchedule table'} 

SQL Server will combine the message and arguments:

      Msg 50002, Level 16, State 1, Line 1      Unable to update LeaseSchedule table. 




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net