Nested Stored Procedures Error Handling
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 sql server nested stored procedure error handling hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges
Nested Stored Procedure In Sql Server 2008 Example
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.
Sql Server Try Catch Nested Stored Procedure
Join them; it only takes a minute: Sign up Nested stored procedures containing TRY CATCH ROLLBACK pattern? up vote 47 down vote favorite 56 I'm interested in the side effects and potential problems of the following pattern: CREATE PROCEDURE
Nested Stored Procedure Example
[Name] AS BEGIN BEGIN TRANSACTION BEGIN TRY [...Perform work, call nested procedures...] END TRY BEGIN CATCH ROLLBACK TRANSACTION RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] END CATCH END To the best of my understanding this pattern is sound when used with a single procedure - the procedure will either complete all of its statements without error, or it will rollback all actions and report the error. However when one stored procedure calls another stored procedure to do nested stored procedure in sql server 2012 some sub-unit of work (with the understanding that the smaller procedure is sometimes called on its own) I see an issue coming about with relation to rollbacks - an informational message (Level 16) is issued stating The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.. This I assume is because the rollback in the sub-procedure is always rolling back the outer-most transaction, not just the transaction started in the sub-procedure. I do want the whole thing rolled back and aborted if any error occurs (and the error reported to the client as an SQL error), I'm just not sure of all the side effects that come from the outer layers trying to rollback a transaction that has already been rolled back. Perhaps a check of @@TRANCOUNT before doing a rollback at each TRY CATCH layer? Finally there is the client end (Linq2SQL), which has it's own transaction layer: try { var context = new MyDataContext(); using (var transaction = new TransactionScope()) { // Some Linq stuff context.SubmitChanges(); context.MyStoredProcedure(); transactionComplete(); } } catch { // An error occured! } In the event that a stored procedure, "MySubProcedure", called inside MyStoredProcedure raises an error, can I be sure that everything previously done in MyStoredProcedure will be rolled back, all the Linq operations made by SubmitChanges will be rolled back, and finally that the error will be logged? Or what do I need to
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss prosedur pengendalian server the workings and policies of this site About Us Learn more about sql server stored procedure error handling best practices Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow prosedur pengendalian pelayan 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 http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern other. Join them; it only takes a minute: Sign up Nested stored procedure exception handling up vote 0 down vote favorite I have one problem.. in every stored procedure i have BEGIN TRY Command and every exception i log into error table. But now i need execute stored procedure inside another stored procedure and i realized that nested http://stackoverflow.com/questions/31875920/nested-stored-procedure-exception-handling exception is populated into parent stored procedure and after that is logged. This is my CATCH construction inside every stored procedure BEGIN BEGIN TRY BEGIN TRANSACTION; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; ... INSERT INTO ErrorLog (ObjectName...) VALUES (OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)...) RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH Example scenario could be SP1 => Fires SP2 => Fires SP3 => Fires SP4 => Fires SP5 If error happened in SP4 into Error table are inserted rows SP1 : Error message from SP4 SP2 : Error message from SP4 SP3 : Error message from SP4 So the problem is that i need SP4 : Error message from SP4 and if it is possible i dont need SP1 : Error message from SP4 SP2 : Error message from SP4 SP3 : Error message from SP4 rows in the error table. What am i doing wrong, what is best practise for error handling ? And sometimes i also get an error that The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION error-handling sql-serve
Java Microsoft & .NET Cloud Open Source PHP Database next Developer.com Database Read More in Database » TIP: Nested Stored Procedure Calls with SQL Server Transactions September 2, 2008 By Michael Klaene Bio » Send Email http://www.developer.com/db/article.php/3768671/TIP-Nested-Stored-Procedure-Calls-with-SQL-Server-Transactions.htm » More Articles » Tweet When developing data-driven software, it is often vital that http://dba.stackexchange.com/questions/82681/how-to-rollback-when-3-stored-procedures-are-started-from-one-stored-procedure you manage transactions to ensure that unexpected errors do not corrupt the database. Transactions allow you to group sets of related database calls in a single, logical unit-of-work. The classic example of debiting one bank account, then crediting another, is often used to illustrate the importance of transactions. If the process blows up somewhere in stored procedure the middle, you want everything put back as it was initially. It is the 'all or nothing' principle. For Microsoft developers who write applications against a SQL Server 2005 database, it is common to write transaction logic in C#, using the System.Data.SqlClient.SqlTransaction class that wraps calls to SQL Server statements or stored procedures. Another option is to utilize MTS (Microsoft Transaction Server) to support distributed transactions. When these approaches are nested stored procedure used, writing T-SQL code to manage transactions typically can be avoided. Sometimes, however, it makes the most sense to manage transactions at the stored procedure level. For example, you might choose to implement a process that requires numerous, process-intensive queries and data manipulation statements as a database stored procedure or a set of procedures. This tip will show you how to avoid transaction-related errors when nesting procedure calls in SQL Server. In its simplest form, here is a SQL Server stored procedure that manages a transaction: CREATE PROCEDURE [dbo].[simple_proc] AS BEGIN BEGIN TRY BEGIN TRANSACTION; PRINT 'Executing simple proc.' --Execute logic within transaction... COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; RAISERROR ('Error in simple proc!',16,1) END CATCH END Post a comment Email Article Print Article Share Articles Digg del.icio.us Slashdot DZone Reddit StumbleUpon Facebook FriendFeed Furl Newsvine Google LinkedIn MySpace Technorati Twitter YahooBuzz A transaction is started. If things go well, changes are committed in the database. Otherwise, the changes are rolled back. This works fine in most situations. That is, unless you have an environment in which you have other stored procedures that invoke this one and they too manage transactions. In such an environment, problems can arise because, though each 'BEGIN TRANSACTION' increm
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 How to rollback when 3 stored procedures are started from one stored procedure up vote 15 down vote favorite 6 I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful. If the first stored procedure works fine in the master stored procedure, but the 2nd stored procedure fails, then will it automatically roll back all the SP's in the master SP or do I have to make some command? Here is my procedure: CREATE PROCEDURE [dbo].[spSavesomename] -- Add the parameters for the stored procedure here @successful bit = null output AS BEGIN begin transaction createSavebillinginvoice begin Try -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN EXEC [dbo].[spNewBilling1] END BEGIN EXEC [dbo].[spNewBilling2] END BEGIN EXEC [dbo].[spNewBilling3] END set @successful = 1 end Try begin Catch rollback transaction createSavesomename insert into dbo.tblErrorMessage(spName, errorMessage, systemDate) values ('spSavesomename', ERROR_MESSAGE(), getdate()) return end Catch commit transaction createSavesomename return END GO sql-server stored-procedures transaction share|improve this question edited Nov 14 '14 at 18:26 Shanky 9,04621332 asked Nov 14 '14 at 15:40 user2483342 86114 If spNewBilling3 throws an error, but you do not want to roll back spNewBilling2 or spNewBilling1, then just remove [begin|rollback|commit] transaction createSavebillinginvoice from spSavesomename. –Mike Nov 16 '14 at 5:58 add a comment| 2 Answers 2 active oldest votes up vote 31 down vote Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will