Error Handling In Nested Stored Procedures Sql Server
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
Error Handling In Sql Server 2008 Stored Procedures
Learn more about Stack Overflow the company Business Learn more about hiring developers or sql server 2005 stored procedure error handling posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow sql server stored procedure error handling best practices 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 Nested stored procedures containing TRY CATCH ROLLBACK pattern?
Mysql Stored Procedure Error Handling
up vote 47 down vote favorite 56 I'm interested in the side effects and potential problems of the following pattern: CREATE PROCEDURE [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 -
Oracle Stored Procedure Error Handling
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 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.MyStored
0 I have nested stored procedures and I have created system defined error numbers and would like to raise an error in the nested stored procedure. However when I run this the error number raised in the parent stored procedure is 50000 and not the defined error number. Has anyone come across sql stored procedure try catch this and know how to handle it? Many thanks more ▼ 0 total comments 337 characters
Nested Stored Procedure In Sql Server 2008 Example
/ 60 words asked Jan 08, 2013 at 12:36 PM in Default Mrs_Fatherjack 5.1k ● 64 ● 66 ● 76 edited Sep 01, 2013 at nested stored procedure example 09:42 AM Fatherjack ♦♦ 43.7k ● 79 ● 98 ● 117 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 2 answers: sort voted first ▼ oldest newest http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern voted first 0 Are you specifying an error message in the parent proc? If so then that gets an error number of 50000 From BOL: When msg_str is specified, RAISERROR raises an error message with an error number of 50000. more ▼ 4 total comments 208 characters / 38 words answered Jan 08, 2013 at 01:18 PM Kev Riley ♦♦ 64k ● 48 ● 61 ● 81 Is there no way round this? Jan 08, 2013 at 01:30 PM Mrs_Fatherjack In SQL 2012, you https://ask.sqlservercentral.com/questions/97302/nested-stored-procedures-and-error-handling.html can use THROW : http://msdn.microsoft.com/en-us/library/ee677615.aspx Jan 08, 2013 at 01:42 PM Kev Riley ♦♦ But I'm guessing this isn't 2012 :( Jan 08, 2013 at 01:43 PM Kev Riley ♦♦ Nope, SQL 2008 R2. Thanks. Jan 08, 2013 at 01:48 PM Mrs_Fatherjack add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 0 Are you using a TRY/CATCH in the outer procedure? You should be able to catch the error from the inner procedure and then ensure that is what is passed back to the calling application. more ▼ 6 total comments 264 characters / 36 words answered Jan 08, 2013 at 01:20 PM Grant Fritchey ♦♦ 137k ● 20 ● 43 ● 81 Yes, using try/catch in outer and nested, but it's not returning the correct error number, it's returning 50000. Jan 08, 2013 at 01:30 PM Mrs_Fatherjack This absolutely works: DROP PROCEDURE dbo.Wrapper; go CREATE PROCEDURE dbo.Wrapper AS BEGIN TRY EXEC dbo.Inner1; END TRY BEGIN CATCH SELECT ERROR_NUMBER(); END CATCH GO DROP PROCEDURE dbo.Inner1; GO CREATE PROCEDURE dbo.Inner1 AS RAISERROR (13001,15,1); GO EXEC dbo.Wrapper; Jan 08, 2013 at 01:42 PM Grant Fritchey ♦♦ Hi, we have a TRY CATCH in Inner1 so that we can pick up any other errors and when we do this the error number doesn't bubble up to the parent stored procedure properly, am I missing something? Jan 08, 2013 at 01:48 PM Mrs_Fatherjack Ah yes, but I think
Java Microsoft & .NET Cloud Open Source PHP Database next Developer.com Database Read More in Database » TIP: Nested Stored http://www.developer.com/db/article.php/3768671/TIP-Nested-Stored-Procedure-Calls-with-SQL-Server-Transactions.htm Procedure Calls with SQL Server Transactions September 2, 2008 By Michael Klaene Bio » Send Email » More Articles » Tweet When developing data-driven software, it is often vital that you manage transactions to ensure that unexpected errors do not corrupt the database. Transactions allow you to group sets of related database calls stored procedure 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 the middle, you want everything put back as it was initially. It is the 'all or nothing' principle. For Microsoft developers who stored procedure error 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 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 ROLLB