Error Handling In Sql 2008 Stored Procedures
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 error handling in stored procedure sql server 2008 Overflow the company Business Learn more about hiring developers or posting ads with us
T Sql Error Handling Stored Procedures
Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community exception handling in sql server 2008 stored procedure example of 4.7 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
Mysql Stored Procedure Error Handling
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 oracle stored procedure error handling 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,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 RA
Errors in SQL Server 2012 03 January 2013Handling Errors in SQL Server 2012The error handling of SQL Server has always been
Sql Stored Procedure Try Catch
somewhat mysterious. Now at last, the THROW statement has been included in
Sql Trigger Error Handling
SQL Server 2012 that, combined with the TRY ... CATCH block, makes error handling far easier. Robert Sheldon explains sql function error handling all. 194 14 Robert Sheldon Since the release of SQL Server 2005, you've been able to handle errors in your T-SQL code by including a TRY…CATCH block that controls the flow http://stackoverflow.com/questions/14203256/stored-procedure-error-handling-clean-up-but-return-original-error of your script should an error occur, similar to how procedural languages have traditionally handled errors. The TRY…CATCH block makes it easy to return or audit error-related data, as well as take other actions. And within the block-specifically, the CATCH portion-you've been able to include a RAISERROR statement in order to re-throw error-related data to the calling application. However, with the release https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/ of SQL Server 2012, you now have a replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data. In this article, we'll look at the TRY…CATCH block used with both the RAISERROR and THROW statements. The examples are based on a table I created in the AdventureWorks2012 sample database, on a local instance of SQL Server 2012. Listing 1 shows the T-SQL script I used to create the LastYearSales table. 123456789101112131415161718 USE AdventureWorks2012;GOIF OBJECT_ID('LastYearSales', 'U') IS NOT NULLDROP TABLE LastYearSales;GOSELECTBusinessEntityID AS SalesPersonID,FirstName + ' ' + LastName AS FullName,SalesLastYearINTOLastYearSalesFROMSales.vSalesPersonWHERESalesLastYear > 0;GO Listing 1: Creating the LastYearSales table The script should be fairly straightforward. I use a SELECT…INTO statement to retrieve data from the Sales.vSalesPerson view and insert it into the newly created table. However, to demonstrate how to handle errors, we need to add one more element to our table: a check constraint that ensures the SalesLastYear value is never less than zero. Listing 2 shows the ALTERTABLE statement I used to add the constraint. 123 ALTER TABLE LastYearSalesADD CONSTRAINT ckSalesTotal CHECK (SalesLastYear >= 0);GO Listing 2: Add
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 http://dba.stackexchange.com/questions/34275/handling-exceptions-in-stored-procedures-called-using-insert-exec-blocks 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 Handling exceptions in stored procedures called using error handling insert-exec blocks up vote 9 down vote favorite I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The following code illustrates the problem. What I want to do is return 0 or -1 depending on the success or failure of the internal exec() call: alter procedure test -- or create as begin try declare @retval int; -- This code assumes error handling in that PrintMax exists already so this generates an error exec('create procedure PrintMax as begin print ''hello world'' end;') set @retval = 0; select @retval; return(@retval); end try begin catch -- if @@TRANCOUNT > 0 commit; print ERROR_MESSAGE(); set @retval = -1; select @retval; return(@retval); end catch; go declare @t table (i int); insert into @t exec('test'); select * from @t; My problem is the return(-1). The success path is fine. If I leave out the try/catch block in the stored procedure, then the error is raised and the insert fails. However, what I want to do is to handle the error and return a nice value. The code as is returns the message: Msg 3930, Level 16, State 1, Line 6 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. This is perhaps the worst error message I've encountered. It seems to really mean "You did not handle an error in a nested transaction." If I put in the if @@TRANCOUNT > 0, then I get the message: Msg 3916, Level 16, State 0, Procedure gordontest, Line 7 Cannot use the COMMIT statement within an INSERT-EXEC statement unless BEGIN TRANSACTION is used first. I've tried playing around with begin/commit transaction statements, but nothing seems to work. So, how can I have my stored procedure handle errors without aborting the overall transaction? Edit in response to Martin: The actual calling code is