How To Return Error Message From Stored Procedure In Asp.net
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 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
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 or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x 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 Catch error message returned from sql server https://support.microsoft.com/en-us/kb/321903 stored procedure at front end up 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 http://stackoverflow.com/questions/6812254/catch-error-message-returned-from-sql-server-stored-procedure-at-front-end occurs it will return error message. I am calling 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 453k938671031 asked Jul 25 '11 at 5:57 Microsoft Developer 1,5731468116 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 statemen
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 http://stackoverflow.com/questions/26728041/how-to-capture-error-in-sql-server-stored-procedure Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs http://www.4guysfromrolla.com/webtech/tips/t100901-1.shtml Documentation Tags Users Badges Ask Question x 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 capture error in SQL Server stored procedure? up vote 0 down vote favorite I need to capture the error and send how to it to the web application. For example, the below is my stored procedure: CREATE PROC ADDNAME (@FirstName VARCHAR(10), @LastName VARCHAR(10)) AS BEGIN BEGIN TRY BEGIN TRAN INSERT INTO EMPLOYEE(FirstName, LastName) VALUES (@FirstName, @LastName) END TRY BEGIN CATCH ROLLBACK TRAN PRINT @@ERRORMESSAGE END CATCH END Here, how to capture the error and send it to the web application? Normally, which is the best method to handle stored procedure errors from web application? I am using how to return ASP.NET & C# as a front end. I need your suggestions. sql-server-2008 stored-procedures error-handling share|improve this question edited Nov 4 '14 at 6:20 asked Nov 4 '14 at 5:01 thevan 3,4683494153 you can use RAISERROR(Transact-SQL) try this link msdn.microsoft.com/en-us/library/ms178592.aspx –Andamon A. Abilar Nov 4 '14 at 5:16 Yes. In catch block, I generate the error by the things specified in the above link. But how do we send the error to the front end? –thevan Nov 4 '14 at 5:23 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted First, you cannot rollback in BEGIN CATCH. Not without checking XACT_STATE() first. Unless the xact_state() is 1, you cannot run ROLLBACK. Think about the trivial case when the exception is 1205 (a deadlock has occurred) in which case you get the exception after your transaction was forcefully rolled back. See Exception handling and nested transactions for a correct pattern that mixes transactions and error handling. Second, your stored procedure does not need transaction and error handling as is. Unless the real code is much more complex, your procedure doesn't add any value. Lastly, to raise an error use RAISERROR(). SQL Server 2012 has also THROW. share|improve this answer answered Nov 4 '14 at 5:24 Remus Rusanu 207k25268405 Actually, I just writte
Tutorials MSDN Communities Hub Official Docs Security Stump the SQL Guru! XML Info Information: Feedback Author an Article User Tips: Using Return Values from a SQL Server Stored Procedure to Customize Error Messages This tip comes from Pete Draigh When I started developing web pages that interact with a SQL Server database, I probably started like everbody else: with inline SQL statements. I then progressed to using the connection object to call stored procedures and eventually started using the command object. I eventually realized how useful return values from stored procedures could be, since I could use them to return a value based on a potential error condition that I check for in the stored procedure. Recently, I was developing an online catalog and had a situation to deal with: User enters data into a form Need to validate the entries (easy enough with client-side javascript) Need to insert the data into a SQL Server database after checking to make sure various conditions don't exist. For example, the user could enter a product, but only if the product doesn't already exist in the catalog. That's not something that's easily accomplished with client-side validation! Initially I decided upon a fairly popular route: create a form in Page1.asp that submits to Page2.asp which attempts to insert the user-entered information into the database. If the product already exists, go back to Page1.asp, displaying a message and populating the fields with what the user entered. While this is a possible approach, trust me when I say that it's a pain to code if you have a lot of form fields! Ideally I wanted a pop-up message that I could customize based on the condition found in the stored procedure. (I like pop-ups because by their nature, they draw more attention than a m