{"id":1548,"date":"2013-01-03T00:00:00","date_gmt":"2013-01-03T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/handling-errors-in-sql-server-2012\/"},"modified":"2021-08-24T13:40:01","modified_gmt":"2021-08-24T13:40:01","slug":"handling-errors-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/handling-errors-in-sql-server-2012\/","title":{"rendered":"Handling Errors in SQL Server 2012"},"content":{"rendered":"
\n

Since the release of SQL Server 2005, you’ve been able to handle errors in your T-SQL code by including a TRY…CATCH<\/strong> block that controls the flow of your script should an error occur, similar to how procedural languages have traditionally handled errors. The TRY…CATCH<\/strong> block makes it easy to return or audit error-related data, as well as take other actions. And within the block-specifically, the CATCH<\/strong> portion-you’ve been able to include a RAISERROR<\/strong> statement in order to re-throw error-related data to the calling application. However, with the release of SQL Server 2012, you now have a replacement for RAISERROR<\/strong>, the THROW<\/strong> statement, which makes it easier than ever to capture the error-related data. <\/p>\n

In this article, we’ll look at the TRY…CATCH<\/strong> block used with both the RAISERROR<\/strong> and THROW<\/strong> statements. The examples are based on a table I created in the AdventureWorks2012<\/strong> sample database, on a local instance of SQL Server 2012. Listing 1 shows the T-SQL script I used to create the LastYearSales<\/strong> table. <\/p>\n

USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('LastYearSales', 'U') IS NOT NULL\nDROP TABLE LastYearSales;\nGO\n\nSELECT\n  BusinessEntityID AS SalesPersonID,\n  FirstName + ' ' + LastName AS FullName,\n  SalesLastYear\nINTO\n  LastYearSales\nFROM\n  Sales.vSalesPerson\nWHERE\n  SalesLastYear > 0;\nGO<\/pre>\n
Listing 1: Creating the LastYearSales table <\/p>\n
The script should be fairly straightforward. I use a SELECT…INTO<\/strong> statement to retrieve data from the Sales.vSalesPerson<\/strong> view and insert it into the newly created table. However, to demonstrate how to handle errors, we need to add one more element to our table: a check constraint that ensures the SalesLastYear<\/strong> value is never less than zero. Listing 2 shows the ALTERTABLE<\/strong> statement I used to add the constraint. <\/p>\n
ALTER TABLE LastYearSales\nADD CONSTRAINT ckSalesTotal CHECK (SalesLastYear >= 0);\nGO<\/pre>\n
Listing 2: Adding a check constraint to the LastYearSales table <\/p>\n
The constraint makes it easy to generate an error when updating the table. All I have to do is try to add a negative amount to the SalesLastYear<\/strong> column, an amount large enough to cause SQL Server to throw an error. Once we’ve created our table and added the check constraint, we have the environment we need for the examples in this article. You can just as easily come up with your own table and use in the examples. Just be sure you have a way of violating a constraint or you come up with another mechanism to generate an error. The goal is to create a script that handles any errors. <\/p>\n
Working with the TRY…CATCH Block<\/h1>\n
Once we’ve set up our table, the next step is to create a stored procedure that demonstrates how to handle errors. The procedure, UpdateSales<\/strong>, modifies the value in the SalesLastYear<\/strong> column in the LastYearSales<\/strong> table for a specified salesperson. It works by adding or subtracting an amount from the current value in that column. Listing 3 shows the script I used to create the procedure. Notice that I include two input parameters-@SalesPersonID<\/strong> and @SalesAmt<\/strong>-which coincide with the table’s SalesPersonID<\/strong> and SalesLastYear<\/strong> columns. <\/p>\n
USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('UpdateSales', 'P') IS NOT NULL\nDROP PROCEDURE UpdateSales;\nGO\n\nCREATE PROCEDURE UpdateSales\n  @SalesPersonID INT,\n  @SalesAmt MONEY = 0\nAS\nBEGIN\n  BEGIN TRY\n    BEGIN TRANSACTION;\n      UPDATE LastYearSales\n      SET SalesLastYear = SalesLastYear + @SalesAmt\n      WHERE SalesPersonID = @SalesPersonID;\n    COMMIT TRANSACTION;\n  END TRY\n  BEGIN CATCH\n    IF @@TRANCOUNT > 0\n    ROLLBACK TRANSACTION;\n\n    DECLARE @ErrorNumber INT = ERROR_NUMBER();\n    DECLARE @ErrorLine INT = ERROR_LINE();\n    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();\n    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();\n    DECLARE @ErrorState INT = ERROR_STATE();\n\n    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));\n    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));\n\n    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);\n  END CATCH\nEND;\nGO<\/pre>\n
Listing 3: Creating a stored procedure that contains a Try…Catch block <\/p>\n
The main body of the procedure definition, enclosed in the BEGIN…END<\/strong> block, contains the TRY…CATCH<\/strong> block, which itself is divided into the TRY<\/strong> block and the CATCH<\/strong> block. The TRY<\/strong> block starts with BEGINTRY<\/strong> and ends with ENDTRY<\/strong> and encloses the T-SQL necessary to carry out the procedure’s actions. In this case, I include an UPDATE<\/strong> statement that adds the @SalesAmount<\/strong> value to the SalesLastYear<\/strong> column. The statement is enclosed in BEGINTRANSACTION<\/strong> and COMMITTRANSACTION<\/strong> statements to explicitly start and commit the transaction. Examples vary in terms of where they include the transaction-related statements. (Some don’t include the statements at all.) Just keep in mind that you want to commit or rollback your transactions at the appropriate times, depending on whether an error has been generated. <\/p>\n
If the UPDATE<\/strong> statement runs successfully, the SalesLastYear<\/strong> value is updated and the operation is completed, in which case, the code in the CATCH<\/strong> block is never executed. However, if the UPDATE<\/strong> statement fails and SQL Server generates an error, the transaction is terminated and the database engine jumps to the CATCH<\/strong> block. The CATCH<\/strong> block starts with BEGINCATCH<\/strong> and ends with ENDCATCH<\/strong> and encloses the statements necessary to handle the error. <\/p>\n
For the stored procedure in Listing 3, the first step I take in the CATCH<\/strong> block is to roll back the transaction if it is still running. I start by using the @@TRANCOUNT<\/strong> function to determine whether any transactions are still open. @@TRANCOUNT<\/strong> is a built-in SQL Server function that returns the number of running transactions in the current session. In this case, there should be only one (if an error occurs), so I roll back that transaction. <\/p>\n
Next, I declare a set of variables based on system functions that SQL Server makes available within the scope of the CATCH<\/strong> block. The functions return error-related information that you can reference in your T-SQL statements. Currently, SQL Server supports the following functions for this purpose: <\/p>\n