Home > Sql Server > Sql Server Stored Procedure Error Handling Best Practices

Sql Server Stored Procedure Error Handling Best Practices

Contents

Manage Your Profile | Site Feedback Site Feedback x Tell us about your experience... TRY-CATCH The main vehicle for error handling is TRY-CATCH, very reminiscent of similar constructs in other languages. It is also important to communicate that an error has occurred, lest that the user thinks that the operation went fine, when your code in fact performed nothing at all. Yes No Tell us more Flash Newsletter | Contact Us | Privacy Statement | Terms of Use | Trademarks | © 2016 Microsoft © 2016 Microsoft check my blog

The stored procedure usp_GenerateError executes a DELETE statement inside a TRY block that generates a constraint violation error. COMMIT TRANSACTION; END TRY BEGIN CATCH SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage; -- Test XACT_STATE for 1 or -1. -- XACT_STATE = 0 means there is no transaction and -- I discuss ROLLBACK more in the section ROLLBACK or not to ROLLBACK. This is a sin that can have grave consequences: it could cause the application to present incorrect information to the user or even worse to persist incorrect data in the database. https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

Sql Server Stored Procedure Error Handling Best Practices

The quick answer on when to roll back is that if you want maximum simplicity: whenever you get a non-zero value in @@error or a non-zero return value from a stored Errno ' + ltrim(str(@errno)) + ': ' + @errmsg The purpose of this SELECT statement is to format an error message that we pass to RAISERROR, and which includes all information PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + ', State ' + CONVERT(varchar(5), ERROR_STATE()) + ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + ', Line ' To reduce the risk for this accident, always think of the command as ;THROW.

The return value of a stored procedure can be retrieved and an error can be handled on that level as well. If you use old ADO, I cover this in my old article on error handling in SQL2000. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored. Sql Server Error_message And that is about any statement in T-SQL.

EXECUTE usp_MyErrorLog; IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; END CATCH; END; -- End WHILE loop. After I declare the variables, I include two PRINT statements that display the values of the @ErrorNumber and @ErrorLine variables (along with some explanatory text). I still like the idea from the perspective of robust programming. https://technet.microsoft.com/en-us/library/aa175920(v=sql.80).aspx Acknowledgements and Feedback Thanks to Thomas Hummel who pointed out a weakness in error_demo_cursor.

Having shown how to handle date-based information using the Multi-dimensional model, Dennes now turns his attention on the in-memory tabular model.… Read more [email protected] Thank you Thanks for providing the article. T-sql Raiserror CREATE TABLE my_sales ( Itemid INT PRIMARY KEY, Sales INT not null ); GO INSERT my_sales (itemid, sales) VALUES (1, 1); INSERT my_sales (itemid, sales) VALUES (2, 1); GO -- Verify CREATE TABLE my_books ( Isbn int PRIMARY KEY, Title NVARCHAR(100) ); GO BEGIN TRY BEGIN TRANSACTION; -- This statement will generate an error because the -- column author does not exist I will try to get it republished somewhere and update the link.] share|improve this answer edited Sep 30 '09 at 17:53 answered Apr 7 '09 at 15:02 Rob Garrison 4,73821419

Error Handling In Sql Server 2012

Having read all the theory, let's try a test case: EXEC insert_data 9, NULL The output is: Msg 50000, Level 16, State 1, Procedure insert_data, Line 12 Cannot insert the value Nice post, good information. Sql Server Stored Procedure Error Handling Best Practices The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1. Sql Try Catch Throw For the same reason, my experience of ADO and ADO .Net programming is not in par with my SQL knowledge .

GO TRY…CATCH with RAISERRORRAISERROR can be used in either the TRY or CATCH block of a TRY…CATCH construct to affect error-handling behavior.RAISERROR that has a severity of 11 to 19 executed click site Yes No Do you like the page design? Thanks, Rebeccah Pingback: Less Than Dot - Blog - Awesome Pingback: Less Than Dot - Blog - Awesome Leave a Reply Cancel reply Your email address will not be published. i'm feeling proud while writhing this testimonial. Sql Server Try Catch Transaction

A TRY…CATCH Example: Retrying After Deadlocks Sometimes, it may make sense to use TRY…CATCH blocks to retry the execution of a statement, after a deadlock. As soon as this code tries to execute, a deadlock is detected. I am not suggesting that we abandon T-SQL error handling; far from it. http://odenews.net/sql-server/try-catch-in-sql-server-stored-procedure.html It is every Database Developer's nightmare.

Say that another programmer calls your code. Sql @@trancount IF ERROR_NUMBER() IS NULL RETURN; DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(200); -- Assign variables to error-handling functions that -- capture information for RAISERROR. There are situations when checking @@error is unnecessary, or even meaningless.

MS has written in Books online that many features are going to be deprecated and eventually removed.

When you activate XACT_ABORT ON, almost all errors have the same effect: any open transaction is rolled back and execution is aborted. Cannot insert duplicate key in object 'dbo.sometable'. The recommendations are based from how SQL2000 works, but they apply equally well to SQL7 and SQL6.5. (The situation in SQL6.5 is actually slightly less complex, but since you presumably will Sql Try Catch Rollback Doing this in each and every CATCH handler would be a gross sin of code duplication, and there is no reason to.

IF @mode NOT IN ('A', 'B', 'C') BEGIN RAISERROR('Illegal value "%s" passed for @mode.', 16, -1, @mode) RETURN 50000 END INSERT #temp (...) SELECT ... Shailendra always teaches latest technologies. Note, however, that this stored procedure does not attempt to determine whether or not either of the two modifications failed, and it does not handle possible errors. More about the author GO Copy USE AdventureWorks2008R2; GO -- Declare and set variable -- to track number of retries -- to try before exiting.