Catch Error In Sql Stored Procedure
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 sql server stored procedure try catch error handling site About Us Learn more about Stack Overflow the company Business Learn more
Raise Error Sql Stored Procedure
about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x sql stored procedure error log Dismiss Join the Stack Overflow 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 How to
Sql Stored Procedure Error Line Number
get sql error in stored procedure up vote 5 down vote favorite 3 I'm using SQL Server 2005. I created a stored procedure which works most of the time, but I found an instance of where it doesn't do what I want. Currently, the code does something like this if @@error <> 0 begin select @message_error = "There was a database error adding product "+ sql stored procedure error invalid object name @product + " to product line end Where @message_error is an output variable. So, I can select @@error and get a number, but all I really want is the SQL error. Something like Hey, I couldn't do this because there is a fk constraint on this column or whatever. I found this article on msdn http://msdn.microsoft.com/en-us/library/ms178592(v=sql.90).aspx But it only goes over throwing custom exceptions with RAISERROR, I don't want to create my own error message or exception, I just want to know why stuff isn't working. I can execute the stored procedure through Management Studio and see the exact SQL error, but this is tedious trying to match data from the site and manually inserting it that way. How do I get the SQL error text into an output variable? sql sql-server tsql sql-server-2005 stored-procedures share|improve this question edited Nov 30 '12 at 14:53 marc_s 450k918621027 asked Nov 30 '12 at 14:47 Steve G 2,39452347 2 Have you looked at ERROR_MESSAGE msdn.microsoft.com/en-us/library/ms190358.aspx? –Romhein Nov 30 '12 at 14:54 add a comment| 3 Answers 3 active oldest votes up vote 8 down vote accepted Here's part of a stored procedure templ
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
Sql Stored Procedure Return Error
Stack Overflow the company Business Learn more about hiring developers or posting ads
Sql Stored Procedure Return Error Message
with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow mysql stored procedure error is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Catch error message returned from sql server stored procedure at front end up http://stackoverflow.com/questions/13647437/how-to-get-sql-error-in-stored-procedure vote 1 down vote favorite 2 I am writing stored procedure in following way.. CREATE PROCEDURE spTest @intCompId int, @varCompName varchar(50) AS BEGIN BEGIN TRANSACTION BEGIN TRY INSERT INTO tblCompanyMaster(CompId,CompName) VALUES (@intCompId,@varCompName) IF(@@ERROR<>0) RAISERROR('Error',10,1) SELECT ERROR_MESSAGE() IF(@@ERROR=0) BEGIN COMMIT TRANSACTION SELECT 0; END ELSE ROLLBACK TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH END GO I this case if error occurs it will return error message. I am calling http://stackoverflow.com/questions/6812254/catch-error-message-returned-from-sql-server-stored-procedure-at-front-end this sp from front end with sqlcommand property like oSqlcommand.executeNonQuery(); it will return no of rows affected and zero otherwise. Now what I want is if there is any error arise in stored procedure than the message returned by error_message() should be caught at front end as an exception and because I am writing each exception in text file. How can I catch this message at front end?? My coding style is as below.... try { //Some code } catch(exception ex) { //Write exception in text file. } I want to handle that message at ex. asp.net sql-server stored-procedures share|improve this question edited Jul 25 '11 at 6:10 marc_s 450k918621027 asked Jul 25 '11 at 5:57 Microsoft Developer 1,5631468116 add a comment| 1 Answer 1 active oldest votes up vote 5 down vote accepted Your stored procedure contains a fundamental flaw: If you execute any statement after examining the value of @@ERROR (or @@ROWCOUNT), it gets reset to zero. You should always cache in a local variable before continuing: DECLARE @errornum int SET @errornum = @@ERROR If you need rowcount as well, then you must perform in a single statement like so SELECT @errornum = @@ERROR, @rowcount = @@ROWCOUNT In addition, you should not need to mix
| 2 Comments | Tags: Stored Procedures The following article introduces the basics of handling errors http://www.sqlteam.com/article/handling-errors-in-stored-procedures in stored procedures. If you are not familiar with the difference http://www.sommarskog.se/error_handling/Part1.html between fatal and non-fatal errors, the system function @@ERROR, or how to add a custom error with the system stored procedure sp_addmessage, you should find it interesting. The examples presented here are specific to stored procedures as they are the desired method of stored procedure 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) 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 sql stored procedure 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 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 t
This part is also available in a Spanish translation by Geovanny Hernandez. Introduction This article is the first in a series of three about error and transaction handling in SQL Server. The aim of this first article is to give you a jumpstart with error handling by showing you a basic pattern which is good for the main bulk of your code. This part is written with the innocent and inexperienced reader in mind, why I am intentionally silent on many details. The purpose here is to tell you how without dwelling much on why. If you take my words for your truth, you may prefer to only read this part and save the other two for a later point in your career. On the other hand, if you question my guidelines, you certainly need to read the other two parts, where I go into much deeper detail exploring the very confusing world of error and transaction handling in SQL Server. Parts Two and Three, as well as the three appendixes, are directed towards readers with a more general programming experience, although necessarily not with SQL Server. This first article is short; Parts Two and Three are considerably longer. Table of Contents Introduction Index of All Error-Handling Articles Why Error Handling? Essential Commands TRY-CATCH SET XACT_ABORT ON General Pattern for Error Handling Three Ways to Reraise the Error Using error_handler_sp Using ;THROW Using SqlEventLog Final Remarks End of Part One Revision History Index of All Error-Handling Articles Here follows a list of all articles in this series: Part One - Jumpstart Error Handling (this article). Part Two - Commands and Mechanisms. Part Three - Implementation. Appendix 1 - Linked Servers. (Extends Part Two.) Appendix 2 - CLR. (Extends both Parts Two and Three.) Appendix 3 - Service Broker. (Extends Part Three.) All the articles above are for SQL2005 and later. For those who still are