If @@error 0 Goto
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings vba on error goto and policies of this site About Us Learn more about Stack Overflow
T-sql Goto
the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation @@error sql 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; sql raiserror it only takes a minute: Sign up Bad practice to use SQL Server's GOTO for error handling? up vote 6 down vote favorite I was reading about error handling in SQL Server in this article, and they suggest using SQL Server's GOTO in certain situations to roll back the transaction. Example: BEGIN TRAN UPDATE Authors SET Phone = '415 354-9866'
@@trancount
WHERE au_id = '724-80-9391' SELECT @intErrorCode = @@ERROR IF (@intErrorCode <> 0) GOTO PROBLEM UPDATE Publishers SET city = 'Calcutta', country = 'India' WHERE pub_id = '9999' SELECT @intErrorCode = @@ERROR IF (@intErrorCode <> 0) GOTO PROBLEM COMMIT TRAN PROBLEM: IF (@intErrorCode <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN END This article was written nearly 10 years ago and I've heard that it's usually a bad idea to use GOTO. Is the above an ok method for error handling in SQL Server? If not, can anyone suggest a better alternative? sql sql-server error-handling goto share|improve this question asked Jun 21 '12 at 15:44 Abe Miessler 43.9k42188332 Did you check this answer? –Helper Jun 24 '12 at 5:01 add a comment| 2 Answers 2 active oldest votes up vote 13 down vote accepted You should be using Try/Catch in SQL 2005+ BEGIN TRY BEGIN TRAN UPDATE Authors SET Phone = '415 354-9866' WHERE au_id = '724-80-9391' UPDATE Publishers SET city = 'Calcutta', country = 'India' WHERE pub_id = '9999' COMMIT TRAN END TRY BEGIN CATC
Microsoft Tech Companion App Microsoft Technical Communities Microsoft Virtual Academy Script Center Server and Tools Blogs TechNet Blogs TechNet Flash Newsletter TechNet sql try catch Gallery TechNet Library TechNet Magazine TechNet Subscriptions TechNet Video TechNet Wiki xact_state() Windows Sysinternals Virtual Labs Solutions Networking Cloud and Datacenter Security Virtualization Downloads Updates Service Packs Security Bulletins
Xact_abort
Windows Update Trials Windows Server 2012 R2 System Center 2012 R2 Microsoft SQL Server 2014 SP1 Windows 8.1 Enterprise See all trials » Related Sites Microsoft Download http://stackoverflow.com/questions/11141814/bad-practice-to-use-sql-servers-goto-for-error-handling 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 (MCSE) Other https://technet.microsoft.com/en-us/library/aa175920(v=sql.80).aspx 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. This article may contain URLs that were valid when origin
SQL Server 2014 Express resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Samples https://social.msdn.microsoft.com/Forums/en-US/458e80c4-861b-4cd3-9cde-774858d2e266/error-handling-how-to-simplify-tsql-using-if-error0-goto-errorhandler-statements?forum=transactsql Retired content We’re sorry. The content you requested has been removed. You’ll be auto http://weblogs.asp.net/alex_papadimoulis/365685 redirected in 1 second. Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Error handling ?how to simplify tsql , using if @@error<>0 goto errorhandler statements?? SQL Server > Transact-SQL Question 0 Sign in to vote hi all, I have proc that if @@error has many if statements ,, i need to reduce the size of proc In my proc IF @@ERROR <> 0 GOTO errorhandler Exec proc ...... IF @@ERROR <> 0 GOTO errorhandler insert into IF @@ERROR <> 0 GOTO errorhandler values IF @@ERROR <> 0 GOTO errorhandler exec proc..... i have many If statements ,, how to reduce size of tsql ?? can i make one if if @@error 0 @@error<>0 and put all exec procs together or put insert statemetns together ?? nested try catch ?? which one do i need to use? Thanks Tuesday, February 08, 2011 7:45 PM Reply | Quote Answers 0 Sign in to vote I have a sample and a link to good and simple article about Error Handling in this blog post How to insert information into multiple related tables and return ID using SQLDataSource Premature optimization is the root of all evil in programming. (c) by Donald Knuth Naomi Nosonovsky, Sr. Programmer-Analyst My blog Marked as answer by uddu Wednesday, February 09, 2011 12:04 AM Tuesday, February 08, 2011 11:47 PM Reply | Quote Moderator All replies 0 Sign in to vote If you use the same error handler, then just a single TRY/CATCH should work for you (you can also nest it, if needed).Premature optimization is the root of all evil in programming. (c) by Donald Knuth Naomi Nosonovsky, Sr. Programmer-Analyst My blog Tuesday, February 08, 2011 8:02 PM Reply | Quote Moderator 0 Sign in to vote If you are on SQL 2000, you don't have much chocie. But you should write you error checks even
code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being the addition of TRY..CATCH blocks. Let's take a look: First, we have the client access code. Pretty straightforward. It will check for validation exceptions (which we'll define as Error 16, State 2), and handle those in a graceful manner. If it's unhandled it will choke.. .NET Code Dim cmd As New SqlCommand() cmd.CommandText = "Approve_Proposal" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = New SqlConnection(Configuration.ConnectionString) Try cmd.Connection.Open() cmd.ExecuteNonQuery() Catch ex As SqlException Dim vEx As New DataValidationException() For i As Integer = 0 To ex.Errors.Count -1 If ex.Errors(i).State = 2 Then 'it is a validation problem vEx.Messages.Add(ex.Errors(i).Message) Else 'it's a bigger problem than validation Throw ex End If Next i Throw vEx 'Since we are here, there were only validation exceptions Finally cmd.Connection.Close() End Try And now the stored proc code, in T-SQL for SQL Server 2000. Note the technique we have to use ... checking only for an error number, using GOTO, etc ... Stored Procedure - 2000 CREATE PROCEDURE Approve_Proposal( @ProposalNum CHAR(8) ,@EmployeeNum CHAR(5) ) AS BEGIN BEGIN TRANSACTION /* since @@ERROR will only return the error from the last statement, to use unified error handling within a procedure, we have to use this every time */ DECLARE @ErrorNum INT /* If we don't record where in the procedure we are when the error is thrown, we'll have no idea how to fix it! */ DECLARE @ErrorStep VARCHAR(25) /* validate input - ProposalNum Note how we have to raise an error, and then GOTO the code */ IF NOT EXISTS(SELECT 1 FROM Proposals WHERE Status='Pending' AND Number=@ProposalNum) BEGIN RAISERROR ('%s does not represent a proposal pending approval.', 16, 2,@ProposalNum) GOTO ERROR_HANDLER END /* validate input - EmployeeNum */ IF NOT EXISTS( SELECT 1 FROM Employees E INNER JOIN Employee_Roles R ON E.Number = R.Employee_Number WHERE E.Number=@EmployeeNum AND R.Role_ID='Approver') BEGIN RAISERROR ('%s is not authorized to approve proposals.', 16, 2, @EmployeeNum) GOTO ERROR_HANDLER END /* Insert the approval Note how we have to set, then try the errornumber */ SET @ErrorStep='Insert Approval' INSERT INTO Proposal_Approvals (Proposal_Number,Employee_Number,[Date]) VALUES (@ProposalNum,@EmployeeNum,GETDATE()) SET @ErrorNum = @@ERROR IF @ErrorNum<>0 GOTO ERROR_HANDLER /* You see the pattern