Mssql Error Handling Stored Procedure
Contents |
Microsoft Tech Companion App Microsoft Technical Communities Microsoft Virtual Academy Script Center Server and Tools Blogs TechNet Blogs TechNet Flash
Sql Server Stored Procedure Error Handling Best Practices
Newsletter TechNet Gallery TechNet Library TechNet Magazine TechNet Subscriptions TechNet error handling in sql server 2012 Video TechNet Wiki Windows Sysinternals Virtual Labs Solutions Networking Cloud and Datacenter Security Virtualization Downloads Updates try catch in sql server stored procedure Service Packs Security Bulletins Windows Update Trials Windows Server 2016 System Center 2016 Windows 10 Enterprise SQL Server 2016 See all trials » Related Sites Microsoft Download
Error Handling In Sql Server 2008
Center TechNet Evaluation Center Drivers Windows Sysinternals TechNet Gallery Training Training Expert-led, virtual classes Training Catalog Class Locator Microsoft Virtual Academy Free Windows Server 2012 courses Free Windows 8 courses SQL Server training Microsoft Official Courses On-Demand Certifications Certification overview MCSA: Windows 10 Windows Server Certification (MCSE) Private Cloud Certification (MCSE) SQL Server Certification
Sql Try Catch Throw
(MCSE) Other resources TechNet Events Second shot for certification Born To Learn blog Find technical communities in your area Support Support options For business For developers For IT professionals For technical support Support offerings More support Microsoft Premier Online TechNet Forums MSDN Forums Security Bulletins & Advisories Not an IT pro? Microsoft Customer Support Microsoft Community Forums United States (English) Sign in Home Library Wiki Learn Gallery Downloads Support Forums Blogs We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Periodicals Microsoft SQL Server Professional June 2000 June 2000 Error Handling in T-SQL: From Casual to Religious Error Handling in T-SQL: From Casual to Religious Error Handling in T-SQL: From Casual to Religious Error Handling in T-SQL: From Casual to Religious TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived and is not being maintained.
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, sql server try catch transaction gives a deeper description of the idiosyncrasies with error handling in SQL Server
Raiserror In Sql Server
and ADO. That article is in some sense part one in the series. However, you can read this article without exception handling in stored procedure in sql server 2012 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 you https://technet.microsoft.com/en-us/library/aa175920(v=sql.80).aspx 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 Error Handling http://www.sommarskog.se/error-handling-II.html 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, you cannot just ig
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 http://stackoverflow.com/questions/12403221/error-handling-in-sql-server-stored-procedures Overflow the company Business Learn more about hiring developers or posting ads with us http://stackoverflow.com/questions/14203256/stored-procedure-error-handling-clean-up-but-return-original-error 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 like you, helping each other. 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 sql server 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 int=0 DECLARE @DataSetCount int=0 DECLARE @countCurrent int=0 DECLARE @countHistorical int=0 IF in sql server (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', 5, 1) END ELSE if @countCurrent=0 BEGIN RAISERROR('There do not appear to be any records in the Historical Tables to transfer for the specified
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 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 like you, helping each other. Join them; it 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. 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 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,62111847 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 Answer