Error Handling Sql Server Procedure
Contents |
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 2008 stored procedure and ADO. That article is in some sense part one in the series. However, you can sql server 2005 stored procedure error handling read this article without reading the background article first, and if you are not a very experienced user of SQL Server, I recommend you
Sql Server Stored Procedure Error Handling Best Practices
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 for
Error Handling In Stored Procedure Sql Server 2012
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 or error handling in sql server user-defined functions 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 have to work. N
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.
Error Handling Sql Server 2008 R2
The aim of this first article is to give you a jumpstart with error error handling sql server 2000 handling by showing you a basic pattern which is good for the main bulk of your code. This part is written mysql stored procedure error handling 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 http://www.sommarskog.se/error-handling-II.html for your truth, you may prefer to only read this part and save the other two for a later point in your career. 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 http://www.sommarskog.se/error_handling/Part1.html 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 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 a
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 http://stackoverflow.com/questions/12403221/error-handling-in-sql-server-stored-procedures 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 takes a minute: Sign up Error Handling in SQL Server Stored Procedures up error handling vote 2 down vote favorite I have a fairly complex SP (logic wise) with nested IF BEGIN END ELSE, there are multiple end points in this nested logic where the logic fails and I RAISERROR and two places that result in success and SQL being actioned. How, at the end of the SP can I trap the errors and do a IF Error Count>0 THEN ROLLBACK DECLARE @errCount error handling sql int=0 DECLARE @DataSetCount int=0 DECLARE @countCurrent int=0 DECLARE @countHistorical int=0 IF (isnumeric(@DataSetID)=(0) OR @DataSetID=(0)) BEGIN RAISERROR('The DataSet specfied does not appear to be valid', 5, 1) END ELSE IF (@Destination='C' OR @Destination='H') BEGIN if Exists (SELECT NULL from tblOpportunityDataSets where DataSetID=@DataSetID) BEGIN SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID) SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID) IF @destination='C' BEGIN if @countCurrent>0 BEGIN RAISERROR('There are already existing records in the Current Tables for the specified DataSet', 5, 1) END ELSE if @countHistorical=0 BEGIN RAISERROR('There do not appear to be any records in the Historical Tables to transfer for the specified Dataset', 5, 1) END ELSE BEGIN -- ENTER TRANSFER CODE INSERT INTO tblRecordsHistorical ( X, Y, Z ) SELECT X, Y, Z FROM tblA WHERE x=y -- Check that record count in both tables match SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID) SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID) IF (@countCurrent<>@countHistorical) BEGIN RAISERROR('There was an error whilst copying the records into the Historical Tables, Source and Destination Record Count do not match', 5, 1) END ELSE BEGIN END END END ELSE IF @Destination='H' BEGIN if @countHistorical>0 BEGIN RAISERROR('There are already existing records in the Historical Tables for the specified DataSet',