515 Error Executing Stored Procedure
Contents |
| 2 Comments | Tags: Stored Procedures The following article introduces the basics of handling errors in stored procedures. If you are not familiar with the difference executing oracle stored procedure between fatal and non-fatal errors, the system function @@ERROR, or how to add executing stored procedure mysql a custom error with the system stored procedure sp_addmessage, you should find it interesting. The examples presented here are specific executing stored procedure sql server to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do (assuming it’s a non-fatal error)
Executing Stored Procedure Db2
is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. Notice that the previous sentence is specific to non-fatal errors. There are two type of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors executing stored procedure parameters do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error. The following example demonstrates how a fatal error affects a procedure. USE tempdb go CREATE PROCEDURE ps_FatalError_SELECT AS SELECT * FROM NonExistentTable PRINT 'Fatal Error' go EXEC ps_FatalError _SELECT --Results-- Server:Msg 208,Level 16,State 1,Procedure ps_FatalError_SELECT,Line 3 Invalid object name 'NonExistentTable'. The SELECT in the procedure references a table that does not exist, which produces a fatal error. The procedure aborts processing immediately after the error and the PRINT statement is not executed. To demonstrate how a non-fatal error is processed, I need to create the following table. USE tempdb go CREATE TABLE NonFatal ( Column1 int IDENTITY, Column2 int NOT NULL ) This example uses a procedure to INSERT a row into NonFatal, but does not include a value for Column2 (defined as NOT NULL). USE tempdb go CREATE PROCEDURE ps_NonFatal_INSERT @Column2 int =NULL AS INSERT NonFatal VALUES (@Column2) PRINT 'NonFatal' go EXEC ps_NonFatal_INSERT --Results-- Server:Msg 515,Level 16,State 2,Procedure ps_NonFatal_INSERT,Line 4 Cannot insert the value NULL into colum
of detail that you can build into your stored procedures. You can choose the level of detail appropriate for your situation. Let's get right to the examples. (Note that all examples use Northwind tables and that we're focusing here executing stored procedure c# on errors, so all tests are for failure cases.) Compatibility: All code works on both
Executing Stored Procedure Sybase
SQL Server 2000 and SQL Server 2005 Beta 2. It does not leverage any SQL Server 2005 features. Level Zero - No
Executing Stored Procedure In Sql Server With Input Parameter
Detection, No Reporting CREATE PROCEDURE dbo.CreateRegion0 ( @RegionID int, @RegionDescription nchar(50) ) AS BEGIN SET NOCOUNT ON INSERT INTO dbo.Region ( RegionID, RegionDescription ) VALUES ( http://www.sqlteam.com/article/handling-errors-in-stored-procedures @RegionID, @RegionDescription ) END GO Procedure CreateRegion0 includes no error detection or reporting. If we execute this with a RegionID that already exists, DECLARE @rtnVal int EXEC @rtnVal = dbo.CreateRegion0 @RegionID = 2, @RegionDescription = N'Western' we get this error Server: Msg 2627, Level 14, State 1, Procedure SPTest_CreateRegion0, Line 9 Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'. The statement has been terminated. and http://rwgarrison.com/rg/Writing/ErrorCheckingPart1.htm @rtnVal is -4. The error from SQL Server is quite helpful but does not give any details. Level One - Error Detection with Minimal Reporting CREATE PROCEDURE dbo.CreateRegion1 ( @RegionID int, @RegionDescription nchar(50) ) AS BEGIN SET NOCOUNT ON DECLARE @error int DECLARE @rowcount int INSERT INTO dbo.Region ( RegionID, RegionDescription ) VALUES ( @RegionID, @RegionDescription ) SELECT @error = @@error, @rowcount = @@rowcount IF @error <> 0 BEGIN RAISERROR('Error %d inserting into Region', 10, 1, @error) RETURN(@error) END ELSE IF @rowcount <> 1 BEGIN RAISERROR('Rowcount %d inserting into Region', 10, 1, @rowcount) WITH SETERROR RETURN(@@error) END RETURN(0) END GO Procedure CreateRegion1 includes error detection and minimal reporting through in-line RAISERROR calls. If we execute this with a RegionID that already exists, DECLARE @rtnVal int EXEC @rtnVal = dbo.CreateRegion1 @RegionID = 2, @RegionDescription = N'Western' we get this error Server: Msg 2627, Level 14, State 1, Procedure SPTest_CreateRegion1, Line 12 Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'. The statement has been terminated. Error 2627 inserting into Region and @rtnVal is 2627. Here, there are two error messages, but the "custom" message doesn't
some extent ADO - behave when an error occurs. The other article, Implementing Error Handling with Stored Procedures, gives advice for how you should check for errors when you write stored http://www.sommarskog.se/error-handling-I.html procedures. Logically, this article is part one, and Implementing... is part two. However, you https://support.microsoft.com/en-us/kb/968829 can read the articles in any order, and if you are relatively new to SQL Server, I recommend that you start with Implementing.... The article here gives a deeper background and may answer more advanced users' questions about error handling in SQL Server. Note: this article was written for SQL2000 and earlier versions. All I have for SQL stored procedure 2005 is unfinished article with a section Jumpstart Error Handling. The content in this article is to some extent applicable to SQL 2005 as well, but you will have to use your imagination to map what I say to SQL 2005. The article includes a short section on TRY-CATCH. I hope to produce a complete article for error handling in SQL 2005 later on. Table of Contents: Introduction The Basics The Anatomy of executing stored procedure an Error Message How to Detect an Error in T-SQL - @@error Return Values from Stored Procedures @@rowcount @@trancount More on Severity Levels What Happens when an Error Occurs? The Possible Actions When Does SQL Server Take which Action? Connection-termination Scope-abortion Statement-termination and Batch-abortion Trigger Context Errors in User-Defined Functions Control Over Error Handling SET XACT_ABORT ARITHABORT, ARITHIGNORE and ANSI_WARNINGS RAISERROR WITH NOWAIT Duplicates Using Linked Servers Retrieving the Text of an Error Message TRY-CATCH in SQL2005 Client-side Error Handling DB-Library ODBC ADO ADO .Net Acknowledgements and Feedback Revision History Introduction In many aspects SQL Server is a very good DBMS that permits you implement powerful solutions with good performance. However, when it comes to error handling... To be blunt: error handling in SQL Server is poor. It is a patchwork of not-always-so-consistent behaviour. It's also weak in that you have fairly little control over error handling, and for advanced error handling like suppressing errors or logging errors, you must take help from the client-side. Unfortunately, depending on which client library you use, you may find that the client library has its own quirks, sometimes painting you into a corner where there is no real good solution. In this article, I will first look at what parts an error message consi
One games Xbox 360 games PC games Windows games Windows phone games Entertainment All Entertainment Movies & TV Music Business & Education Business Students & educators Developers Sale Sale Find a store Gift cards Products Software & services Windows Office Free downloads & security Internet Explorer Microsoft Edge Skype OneNote OneDrive Microsoft Health MSN Bing Microsoft Groove Microsoft Movies & TV Devices & Xbox All Microsoft devices Microsoft Surface All Windows PCs & tablets PC accessories Xbox & games Microsoft Band Microsoft Lumia All Windows phones Microsoft HoloLens For business Cloud Platform Microsoft Azure Microsoft Dynamics Windows for business Office for business Skype for business Surface for business Enterprise solutions Small business solutions Find a solutions provider Volume Licensing For developers & IT pros Develop Windows apps Microsoft Azure MSDN TechNet Visual Studio For students & educators Office for students OneNote in classroom Shop PCs & tablets perfect for students Microsoft in Education Support Sign in Cart Cart Javascript is disabled Please enable javascript and refresh the page Cookies are disabled Please enable cookies and refresh the page CV: {{ getCv() }} English (United States) Terms of use Privacy & cookies Trademarks © 2016 Microsoft