Error Handling Sql Server 2008 Stored Procedure
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have 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 error handling in stored procedure sql server 2005 posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
Sql Server Stored Procedure Error Handling Best Practices
the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes mysql stored procedure error handling 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
Oracle Stored Procedure Error Handling
to perform the clean up, but return the original error if this insert fails (primarily for logging as I 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 sql stored procedure try catch 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 2012. This article gives the long answer: simple-talk.com/sql/database-administration/… –Pondlife Jan 7 '13 at 20:16 1 In SQL Server 2012 you can use THROW(). In SQL Server 2008 you can't throw/re-raise. –Aaron Bertrand Jan 7 '13 at 20:16 1 Can you explain how the selected answer actually solved this problem? What error are you catching that you can re-raise successfully using RAISERROR (not RAISEERROR)? –Aaron Bertrand Jan 7 '13 at 21:11 add a comment| 3 Answers 3 active oldest votes up vote -1 down vote accepted Try the following snippet. DECLARE @errNum int DECLARE @rowCount int BEGIN TRY INSERT INTO [TABLE] (COL1) VALUES ('1") END TRY BEGIN CATCH SET @errNum = @@ERROR SET @rowCount = @@ROWCOUNT RAISEERROR(@errNum) END CATCH share|improve this answer answered Jan
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 of the idiosyncrasies with error handling in SQL Server
Error Handling In Sql Server 2012
and ADO. That article is in some sense part one in the series. However, you
Try Catch In Sql Server Stored Procedure
can read this article without reading the background article first, and if you are not a very experienced user of SQL Server, I recommend error handling in sql server 2008 you to start here. In places there are links to the background article, if 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 http://stackoverflow.com/questions/14203256/stored-procedure-error-handling-clean-up-but-return-original-error 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 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 http://www.sommarskog.se/error-handling-II.html 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 transaction to run for much longer time than intended, leading to blocking and risk that the user loses all his updates when he logs out. In the first section, I summarize the most important points of the material in the background article, so you know under which presumptions you h
This part is also available in a Spanish translation by Geovanny Hernandez. Introduction This article is the first in a series of three about error and transaction handling in SQL Server. The aim of this first article is to give you a http://www.sommarskog.se/error_handling/Part1.html jumpstart with error handling by showing you a basic pattern which is good for the main http://dba.stackexchange.com/questions/34275/handling-exceptions-in-stored-procedures-called-using-insert-exec-blocks bulk of your code. This part is written with the innocent and inexperienced reader in mind, why I am intentionally silent on many details. The purpose here is to tell you how without dwelling much on why. If you take my words for your truth, you may prefer to only read this part and save the other two for a later point in your career. error handling On the other hand, if you question my guidelines, you certainly need to read the other two parts, where I go into much deeper detail exploring the very confusing world of error and transaction handling in SQL Server. Parts Two and Three, as well as the three appendixes, are directed towards readers with a more general programming experience, although necessarily not with SQL Server. This first article is short; Parts Two and Three are considerably longer. Table of Contents Introduction error handling in Index of All Error-Handling Articles Why Error Handling? Essential Commands TRY-CATCH SET XACT_ABORT ON General Pattern for Error Handling Three Ways to Reraise the Error Using error_handler_sp Using ;THROW Using SqlEventLog Final Remarks End of Part One Revision History Index of All Error-Handling Articles Here follows a list of all articles in this series: Part One - Jumpstart Error Handling (this article). Part Two - Commands and Mechanisms. Part Three - Implementation. Appendix 1 - Linked Servers. (Extends Part Two.) Appendix 2 - CLR. (Extends both Parts Two and Three.) Appendix 3 - Service Broker. (Extends Part Three.) All the articles above are for SQL2005 and later. For those who still are on SQL2000, there are two older articles: Error Handling in SQL Server 2000 – a Background. Implementing Error Handling with Stored Procedures in SQL2000. Why Error Handling? Why do we have error handling in our code? There are many reasons. In a forms application we validate the user input and inform the users of their mistakes. These user mistakes are anticipated errors. But we also need to handle unanticipated errors. That is, errors that occur because we overlooked something when we wrote our code. A simple strategy is to abort execution or at least revert to a point where we know that we have full control. It cannot be enough stressed that it is entirely impermissible to ignore an unanticipated error. This is a sin that can
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have 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 us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Handling exceptions in stored procedures called using insert-exec blocks up vote 9 down vote favorite I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The following code illustrates the problem. What I want to do is return 0 or -1 depending on the success or failure of the internal exec() call: alter procedure test -- or create as begin try declare @retval int; -- This code assumes that PrintMax exists already so this generates an error exec('create procedure PrintMax as begin print ''hello world'' end;') set @retval = 0; select @retval; return(@retval); end try begin catch -- if @@TRANCOUNT > 0 commit; print ERROR_MESSAGE(); set @retval = -1; select @retval; return(@retval); end catch; go declare @t table (i int); insert into @t exec('test'); select * from @t; My problem is the return(-1). The success path is fine. If I leave out the try/catch block in the stored procedure, then the error is raised and the insert fails. However, what I want to do is to handle the error and return a nice value. The code as is returns the message: Msg 3930, Level 16, State 1, Line 6 The curr