Error Handling In Stored Procedure Sql Server 2008
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 sql stored procedure try catch or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x error handling in stored procedure sql server 2012 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 try catch in sql server 2008 stored procedure example only 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. t sql stored procedure error handling I'd like it 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
Try Catch In Stored Procedure
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 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
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the
Error Handling In Stored Procedure Sql Server 2005
workings and policies of this site About Us Learn more about try catch in sql server stored procedure Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions error handling in sql server 2012 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. http://stackoverflow.com/questions/14203256/stored-procedure-error-handling-clean-up-but-return-original-error Join them; it only takes a minute: Sign up Error Handling in SQL Server Stored Procedures up 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 http://stackoverflow.com/questions/12403221/error-handling-in-sql-server-stored-procedures 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 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
This part is also available in a Spanish translation by Geovanny Hernandez. Introduction This article is the first in http://www.sommarskog.se/error_handling/Part1.html a series of three about error and transaction handling in SQL Server. The aim of this first article is to give you a jumpstart with error handling by http://www.codeproject.com/Articles/38650/Overview-of-Error-Handling-in-SQL-Server showing you a basic pattern which is good for the main bulk of your code. This part is written with the innocent and inexperienced reader in mind, why I stored procedure 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. On the other hand, if you question my guidelines, you error handling in 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 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.)
Articles Technical Blogs Posting/Update Guidelines Article Help Forum Article Competition Submit an article or tip Post your Blog quick answersQ&A Ask a Question about this article Ask a Question View Unanswered Questions View All Questions... C# questions Linux questions ASP.NET questions SQL questions VB.NET questions discussionsforums All Message Boards... Application Lifecycle> Running a Business Sales / Marketing Collaboration / Beta Testing Work Issues Design and Architecture ASP.NET JavaScript C / C++ / MFC> ATL / WTL / STL Managed C++/CLI C# Free Tools Objective-C and Swift Database Hardware & Devices> System Admin Hosting and Servers Java .NET Framework Android iOS Mobile SharePoint Silverlight / WPF Visual Basic Web Development Site Bugs / Suggestions Spam and Abuse Watch features Competitions News The Insider Newsletter The Daily Build Newsletter Newsletter archive Surveys Product Showcase Research Library CodeProject Stuff communitylounge Who's Who Most Valuable Professionals The Lounge The Insider News The Weird & The Wonderful The Soapbox Press Releases Non-English Language > General Indian Topics General Chinese Topics help What is 'CodeProject'? General FAQ Ask a Question Bugs and Suggestions Article Help Forum Site Map Advertise with us About our Advertising Employment Opportunities About Us Articles » Database » Database » SQL Server ArticleBrowse CodeStatsRevisions (2)Alternatives Comments (25) Add your ownalternative version Tagged as ADO.NETDevDesignDBA Stats 144.8K views55 bookmarked Posted 1 Aug 2009 Overview of Error Handling in SQL Server 2005 Abhijit Jana, 1 Aug 2009 CPOL 4.78 (34 votes) 1 2 3 4 5 4.78/5 - 34 votes4 removedμ 4.53, σa 1.44 [?] Rate this: Please Sign up or sign in to vote. Overview of Error and Exception Handling in SQL Server 2005 using @@Error and Try-Catch Table of Contents Introduction When We Need To Handle Error in SQL Server Error Handling Mechanism Using @@ERROR General Synt