Ms Sql Server Stored Procedure Error Handling
Contents |
Errors in SQL Server 2012 03 January 2013Handling Errors in SQL Server 2012The error handling of SQL Server has always been somewhat mysterious. Now at last, the THROW statement has been try catch in sql server stored procedure included in SQL Server 2012 that, combined with the TRY ... CATCH block, makes sql server stored procedure error handling best practices error handling far easier. Robert Sheldon explains all. 195 14 Robert Sheldon Since the release of SQL Server 2005, you've been able sql server error handling to handle errors in your T-SQL code by including a TRY…CATCH block that controls the flow of your script should an error occur, similar to how procedural languages have traditionally handled errors. The TRY…CATCH block error handling in sql server 2012 makes it easy to return or audit error-related data, as well as take other actions. And within the block-specifically, the CATCH portion-you've been able to include a RAISERROR 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, the THROW statement, which makes it easier than ever to capture the error-related data. In this article, we'll
Sql Server Try Catch Transaction
look at the TRY…CATCH block used with both the RAISERROR and THROW statements. The examples are based on a table I created in the AdventureWorks2012 sample database, on a local instance of SQL Server 2012. Listing 1 shows the T-SQL script I used to create the LastYearSales table. 123456789101112131415161718 USE AdventureWorks2012;GOIF OBJECT_ID('LastYearSales', 'U') IS NOT NULLDROP TABLE LastYearSales;GOSELECTBusinessEntityID AS SalesPersonID,FirstName + ' ' + LastName AS FullName,SalesLastYearINTOLastYearSalesFROMSales.vSalesPersonWHERESalesLastYear > 0;GO Listing 1: Creating the LastYearSales table The script should be fairly straightforward. I use a SELECT…INTO statement to retrieve data from the Sales.vSalesPerson 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 value is never less than zero. Listing 2 shows the ALTERTABLE statement I used to add the constraint. 123 ALTER TABLE LastYearSalesADD CONSTRAINT ckSalesTotal CHECK (SalesLastYear >= 0);GO Listing 2: Adding a check constraint to the LastYearSales table 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 column, an amount large enough to cause SQL Server to throw an error. Once we've created our table and added the check constraint,
how you should implement error handling when you write stored procedures, including when you call them from ADO. The other article, Error Handling in SQL Server - a Background, gives a deeper description
Sql Try Catch Throw
of the idiosyncrasies with error handling in SQL Server and ADO. That article is in error handling in sql server 2008 some sense part one in the series. However, you can read this article without reading the background article first, and if raise error sql you are not a very experienced user of SQL Server, I recommend you to start here. In places there are links to the background article, if you want more information about a certain issue. Note: this https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/ article is aimed at SQL2000 and earlier versions of SQL Server. SQL2005 offers significantly improved methods for error handling with TRY-CATCH. This article is not apt if you are using SQL 2005 or later. I don't have a complete article on error handling for SQL 2005, but I have an unfinished article with a section Jumpstart Error Handling that still can be useful. Table of Contents: Introduction The Presumptions A General http://www.sommarskog.se/error-handling-II.html Example Checking Calls to Stored Procedures The Philosophy of Error Handling General Requirements Why Do We Check for Errors? When Should You Check @@error? ROLLBACK or not to ROLLBACK - That's the Question SET XACT_ABORT ON revisited Error Handling with Cursors Error Handling with Triggers Error Handling with User-Defined Functions Error Handling with Dynamic SQL Error Handling in Client Code What to Do in Case of an Error? Command Timeouts Why is My Error Not Raised? Getting the Return Value from a Stored Procedure Acknowledgements and Feedback Revision History Introduction Error handling in stored procedures is a very tedious task, because T-SQL offers no exception mechanism, or any On Error Goto. All you have is the global variable @@error which you need to check after each statement for a non-zero value to be perfectly safe. If you call a stored procedure, you also need to check the return value from the procedure. In fact, this is so extremely tedious, so you will find that you will have to make compromises and in some situations assume that nothing can go wrong. Still, you cannot just ignore checking for errors, because ignoring an error could cause your updates to be incomplete, and compromise the integrity of your data. Or it can cause a transacti
here for a quick overview of the site Help Center Detailed answers to any questions you might have http://stackoverflow.com/questions/725891/what-is-the-best-practice-use-of-sql-server-t-sql-error-handling Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with http://www.sqlteam.com/article/handling-sql-server-errors us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just sql server like you, helping each other. Join them; it only takes a minute: Sign up What is the best practice use of SQL Server T-SQL error handling? up vote 20 down vote favorite 12 We have a large application mainly written in SQL Server 7.0, where all database calls are to stored procedures. We are now running sql server stored SQL Server 2005, which offers more T-SQL features. After just about every SELECT, INSERT, UPDATE, and DELETE, the @@ROWCOUNT and @@ERROR get captured into local variables and evaluated for problems. If there is a problem the following is done: error message output parameter is set rollback (if necessary) is done info is written (INSERT) to log table return with a error number, unique to this procedure (positive if fatal, negative is warning) They all don't check the rows (only when it is known) and some differ with more or less log/debug info. Also, the rows logic is somethimes split from the error logic (on updates where a concurrency field is checked in the WHERE clause, rows=0 means someone else has updated the data). However, here is a fairly generic example: SELECT, INSERT, UPDATE, or DELETE SELECT @Error=@@ERROR, @Rows=@@ROWCOUNT IF @Rows!=1 OR @Error!=0 BEGIN SET @ErrorMsg='ERROR 20, ' + ISNULL(OBJECT_NAME(@@PROCID), 'unknown') + ' - unable to ???????? the ????.' IF @@TRANCOUNT >0 BEGIN ROLLBACK END SET @LogInfo=ISNULL(@LogIn
| Tags: Stored Procedures, SQL Server 2005, Transactions, SQL Server 2008 Features, Error Handling This article covers the basics of TRY CATCH error handling in T-SQL introduced in SQL Server 2005. It includes the usage of common functions to return information about the error and using the TRY CATCH block in stored procedures and transactions. SQL Server uses the following syntax to capture errors in Transact-SQL statements: BEGIN TRY SELECT [First] = 1 SELECT [Second] = 1/0 SELECT [Third] = 3 END TRY BEGIN CATCH PRINT 'An error occurred' END CATCH This returns the following output: First ----------- 1 (1 row(s) affected) Second ----------- (0 row(s) affected) An error occurred A key difference from SQL Server 2000 is that execution is halted when SQL Server encounters an error. At that point execution transfers to the CATCH block. This error isn't returned to the client application or calling program. The TRY CATCH block consumes the error. Error Functions Inside the CATCH block there are a number of specialized functions to return information about the error. BEGIN TRY SELECT [Second] = 1/0 END TRY BEGIN CATCH SELECT [Error_Line] = ERROR_LINE(), [Error_Number] = ERROR_NUMBER(), [Error_Severity] = ERROR_SEVERITY(), [Error_State] = ERROR_STATE() SELECT [Error_Message] = ERROR_MESSAGE() END CATCH Second ----------- (0 row(s) affected) Error_Line Error_Number Error_Severity Error_State ----------- ------------ -------------- ----------- 2 8134 16 1 (1 row(s) affected) Error_Message --------------------------------------------------- Divide by zero error encountered. (1 row(s) affected) These are the functions you can use inside a CATCH block. These functions all return NULL if they are called from outside a CATCH block. ERROR_NUMBER. The number of the error that occurred. This is similar to @@ERROR except that it will return the same number for the duration of the CATCH block. ERROR_MESSAGE. The complete text of the error message including any substiture parameters such as object names. ERROR_LINE. This is the line number of the batch or stored procedure where the error occured. ERROR_SEVERITY. This is the severity of the error. The CATCH block only fires for errors with severity 11 or higher. Error severities from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 are usually software or hardware errors where processing may not be able to contin