SQL Server

SQL server 2008 : Handling Errors (part 3)

12/3/2010 9:10:31 AM


SQL Server 2005 and 2008 provide you with a powerful error handling feature in your T-SQL code: a TRY and CATCH control of flow statement. This error handling technique is similar to exception handling, which exists in programmability languages. TRY defines a block of code that might raise an error. If any statement in this block generates an error, execution immediately stops, and the code in the CATCH block is run. The syntax of the TRY...CATCH block is as follows:

{ sql_statement | statement_block }
[ { sql_statement | statement_block } ]
[ ; ]

In both blocks, you can place a single statement or a group of statements in a batch or enclosed in a BEGIN...END block. A TRY block must be immediately followed by an associated CATCH block. If you include any other statements between the END TRY and BEGIN CATCH statements, SQL Server will generate a syntax error.

This T-SQL construction has two flows: when there is an error and when there are no errors. If there is an error in your code detected by a TRY block, control passes to the first statement in the associated CATCH block when the error arises. When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. On the other hand, when there are no errors in your TRY block, SQL Server passes control to the statement immediately after the associated END CATCH statement after the last statement in the TRY block is executed. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger. You can use TRY...CATCH blocks in many transactions and objects such as stored procedures and triggers. However, they can’t be used in a user-defined function.

One interesting thing that you should know is that a TRY...CATCH block catches all execution errors with a severity level higher than 10 and that don’t close the connection. Also, you can nest TRY...CATCH blocks. Either a TRY or a CATCH block can contain nested TRY...CATCH constructs, providing great flexibility to your code. However, this construct can’t span multiple batches or multiple blocks of statements. For example, you can’t use two BEGIN...END blocks inside a TRY...CATCH block.

The TRY...CATCH block allows you to handle errors that occur during compilation or statement-level recompilation of your code such as when calling an object that doesn’t exist. In these situations, you can handle these errors by executing the code that generates the error in a separate batch within the TRY block. For example, you can place the code in a stored procedure or use the sp_executesql system procedure to dynamically create your statement. These two methods allow TRY...CATCH to catch the error at a higher level of execution than the error occurrence. Within the CATCH block, you can determine what caused the problem and get information about the error using special T-SQL error handling functions. These functions retrieve data from the sys.messages catalog view. The following list shows system functions you can use to obtain information about the error that caused the CATCH block to be executed:

  • ERROR_NUMBER(): Returns the error ID number.

  • ERROR_SEVERITY(): Returns the severity level of an error.

  • ERROR_STATE(): Returns the error state number.

  • ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger where the error occurred. This is very handy information when you have nested programmability objects, as the procedures or triggers that cause an error may not be the ones to actually handleit.

  • ERROR_LINE(): Returns the line number inside the routine that caused the error.

  • ERROR_MESSAGE(): Returns the complete text of the error message. The great thing about this function is that the text includes the values supplied for any arguments.

By using these functions, you can determine whether you need to use ROLLBACK in your CATCH block to roll back your transaction.

Test Day Tip

It’s important to you review these SQL Server system functions and understand their returns. There will be questions on the exam where knowledge of these functions makes a difference.

Example: Handling Errors with a TRY...CATCH Block

In this example, we will develop a stored procedure that inserts a new credit card in the AdventureWorks2008 database. We will handle its output using a TRY... CATCH block. Figure 5 shows the code used to create the uspInsertValidated-CreditCard_2 procedure.

Figure 5. Creating the uspInsertValidatedCreditCard_2 Procedure

You can see that this code has some important differences from the previous example, which used the @@ERROR function. After the header of the procedure, there is a TRY block with the main code of the procedure composed of INSERT and RAISERROR statements. The normal flow of this procedure would be the insertion of credit card information and the exhibition of a message stating that it completed successfully.

However, an error may arise when a user executes this procedure, and that is when the CATCH block enters. You can see that the code creates three local variables: @Severity, @State, and @Message. These variables are designed to receive values from the error handling functions ERROR_SEVERITY(), ERROR_STATE(), and ERROR_MESSAGE() via a SELECT statement. Then, after the local variable values are set, a custom error message is created using a RAISERROR statement.

To test this new procedure and see how it handles errors, let’s try to insert the same credit card number twice. Figure 6 shows a script that tries to violate the unique constraint of this table.

Figure 6. Inserting Two Credit Cards with the Same Number

The output when you execute the two procedures is really interesting. The first credit card is inserted without any error, and SQL Server shows the successful completion message after its execution. The second credit card has the same number as the first one, so it will raise an error. However, instead of SQL Server showing the message and stopping execution when the error arose, SQL Server stops execution of the INSERT statement and starts to execute the CATCH block. You can see that the error message raised is exactly what was defined in the CATCH block. This means that you can create custom messages as outputs of your code and control SQL Server error messages in an easy and efficient way. Also, observe that the text of the error message brings the arguments filled, avoiding having to inform them in the RAISERROR statement.

Exercise . Handling Errors

In this exercise, you will develop a script that creates a table and handles any possible errors using the @@ERROR function. You will then change your code to include a TRY...CATCH block. Finally, you will execute the script.

  1. Launch SQL Server Management Studio (SSMS), connect to the instance, open a new query window, and change the context to the AdventureWorks2008 database.

  2. Create a table containing a user-defined error message by executing the following code:

    CREATE TABLE [dbo].[TestTable]
    IF @@ERROR != 0
    RAISERROR('There was a problem!', 16, 1)
    RAISERROR('Everything is OK!', 5, 1)
  3. Verify that the table was created by executing the sp_help system procedure, as follows:

    sp_help TestTable
  4. Now, let’s execute the code that creates the TestTable table again. We know that SQL Server will return an error message since the table already exists. But, a strange thing should happen. The output of SQL Server should be the following:

    Msg 2714, Level 16, State 6, Line 1
    There is already an object named 'TestTable' in the database.
  5. You may be asking: Why didn’t SQL Server show my custom error message? The answer is that the @@ERROR function handled the error in the analyze step. To solve this problem, let’s rewrite the code using a TRY...CATCH block, as follows:

    CREATE TABLE [dbo].[TestTable]
    RAISERROR('Everything is OK!', 5, 1)
    RAISERROR('There was a problem!', 16, 1)
  6. Execute the code again. Now SQL Server should display your custom message.

Other -----------------
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 5) - Filtered Indexes
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 4) - Indexing JOIN Criteria
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 3) - Covering Your Queries
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 2) - Clustered Index Seeks
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 1)
- SQL Server Integration Services : Logged and Nonlogged Operations
- SQL Server Integration Services : Using bcp (part 5)
- SQL Server Integration Services : Using bcp (part 4)
- SQL Server Integration Services : Using bcp (part 3)
- SQL Server Integration Services : Using bcp (part 2) - Fundamentals of Exporting and Importing Data
- SQL Server Integration Services : Using bcp (part 1)
- SQL Server Integration Services : Connection Projects in Visual Studio
- SQL Server Integration Services : The Package Execution Utility (part 3) - The dtutil Utility
- SQL Server Integration Services : The Package Execution Utility (part 2) - Running Packages
- SQL Server Integration Services : The Package Execution Utility (part 1)
- SQL Server Integration Services : The SSIS Designer
- SQL Server Integration Services : Running the SSIS Wizard
- SQL Server Integration Services : A Data Transformation Requirement
- SQL Server 2008 : SSIS Tools and Utilities
- SQL Server 2008 : SSIS Architecture and Concepts
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Most View
- Working with Search Page Layouts : Applying a Branded Master Page to a Search Center
- SOA with .NET and Windows Azure : Service Contracts with WCF (part 1)
- BizTalk 2010 Recipes : Business Rules Framework - Calling the Business Rules Engine from dot NET
- Microsoft Exchange Server 2003: Configuring Recipient Objects (part 6) - Configuring Mailbox Permissions
- Windows 7 : Controlling Services with a Script
- SharePoint 2010 : Modify the Top or Left Navigation Bar (part 1)
- BizTalk Server 2009 : Using asynchronous services in WCF (part 1) - Creating the synchronous service
- jQuery 1.3 : Improving a basic form (part 1) - Progressively enhanced form styling
- Developing an SEO-Friendly Website : Keyword Targeting (part 4)
- Windows Server 2008 : Configuring SMTP (part 1) - Installing the SMTP Server Feature