Error Handling In Stored Procedures In Sql Server
Contents |
Errors in SQL Server 2012 03 January 2013Handling Errors in SQL Server 2012The error handling of SQL Server has always been somewhat error handling in sql server 2005 stored procedures mysterious. Now at last, the THROW statement has been included in SQL
Error Handling In Stored Procedure Sql Server 2008
Server 2012 that, combined with the TRY ... CATCH block, makes error handling far easier. Robert Sheldon explains all.
Error Handling In Stored Procedure Sql Server 2012
194 14 Robert Sheldon Since the release of SQL Server 2005, you've been able to handle errors in your T-SQL code by including a TRY…CATCH block that controls the flow of
Sql Server Stored Procedure Error Handling Best Practices
your script should an error occur, similar to how procedural languages have traditionally handled errors. The TRY…CATCH block 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 mysql stored procedure error handling 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 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
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 oracle stored procedure error handling Background, gives a deeper description of the idiosyncrasies with error handling in SQL Server sql stored procedure try catch and ADO. That article is in some sense part one in the series. However, you can read this article without try catch in sql server stored procedure reading the background article first, and if 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 https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/ you want more information about a certain issue. Note: this 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 http://www.sommarskog.se/error-handling-II.html Error Handling that still can be useful. Table of Contents: Introduction The Presumptions A General 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,
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and http://stackoverflow.com/questions/14203256/stored-procedure-error-handling-clean-up-but-return-original-error policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only stored procedure takes a minute: Sign up Stored Procedure Error Handling - Clean up but return original error up vote 1 down vote favorite 1 I'm writing a stored procedure that needs to clean up some data if an insert fails. I'd like it to perform the clean up, but return the original error if this insert fails (primarily for logging as I error handling in want to see exactly why the insert failed). Basically like a throw; in C#. Is there a simple way to do this? BEGIN TRY Insert into table (col1) values ('1") END TRY BEGIN CATCH --do clean up here --then throw original error END TRY Is this feasible/good practice? In the application code that calls the proc, I'm handling the error from an application standpoint, but the clean up statements seem to better fit inside the proc. sql-server-2008 stored-procedures error-handling share|improve this question asked Jan 7 '13 at 20:08 Tim Coker 4,59111847 usually you do roll back and clean up in the catch block. I personally thought that was one of the best uses of catch block in stored procedures. You just need to be sure that any of your roll back/clean up is not going to create more errors and that whatever you are trying to clean up, is malleable after your error. –Pow-Ian Jan 7 '13 at 20:11 1 The short answer is to use RAISERROR because there is no THROW in SQL Server until version 2