Ms Sql Server Stored Procedure Return Error
Contents |
360 games PC games sql server stored procedure raiserror Windows games Windows phone games Entertainment All Entertainment
How To Return Error Message From Stored Procedure In Sql Server 2008
Movies & TV Music Business & Education Business Students & educators how to get error message in sql server stored procedure Developers Sale Sale Find a store Gift cards Products Software & services Windows Office Free downloads & security Internet
Return Error Message From Stored Procedure To C#
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 stored procedure error codes 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
Return Message From Stored Procedure
of this site About Us Learn more about Stack Overflow the company
Sql Server Stored Procedure Error Handling
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users how to find error in stored procedure in oracle Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a https://support.microsoft.com/en-us/kb/321903 minute: Sign up SQL Server stored procedure return code is NULL when called from TRY…CATCH block up vote -1 down vote favorite 1 Let's say I have the following SQL Server stored procedure: CREATE PROC TestSP AS select 'results selected' as column1 RAISERROR('this is an error',16,1) --RETURN 3 If I call it like this, DECLARE @ReturnCode int EXEC @ReturnCode = TestSP select http://stackoverflow.com/questions/33403062/sql-server-stored-procedure-return-code-is-null-when-called-from-try-catch-blo @ReturnCode as ReturnCode the return code will be -6. If I ALTER PROC to uncomment the --RETURN 3 line in the stored procedure, and again call it, the RETURN 3 will override and cause the return code to be 3. If I call the procedure from within TRY...CATCH block, DECLARE @ReturnCode int BEGIN TRY EXEC @ReturnCode = TestSP END TRY BEGIN CATCH PRINT 'An error occurred.' END CATCH SELECT @ReturnCode as ReturnCode the return code will be NULL. Is using a return code (EXEC @ReturnCode = TestSP) not compatible with calling the stored procedure from within a TRY...CATCH block? sql-server error-handling try-catch raiserror share|improve this question asked Oct 28 '15 at 22:59 Baodad 668923 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote The reason the return code is NULL when calling it from a TRY...CATCH block may have something to do with batch abortion. Microsoft SQL Server gives the programmer the option of EXECuting stored procedures in a way that captures the return code, as you said: EXEC @ReturnCode = YourStoredProc but you also have the option
| 2 Comments | Tags: Stored Procedures The following article introduces the basics of handling errors in stored procedures. If you are http://www.sqlteam.com/article/handling-errors-in-stored-procedures not familiar with the difference 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 http://www.4guysfromrolla.com/webtech/tips/t100901-1.shtml it interesting. The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure 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 specific to non-fatal errors. There are two type of errors in SQL Server: fatal and non-fatal. Fatal sql server stored 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 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
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 message displayed on a page.) Also, I wanted the user taken back to Page1.asp with all of his/her entries already filled in. Here is an example stored procedure that returns an error result if something goes awry:
Create Procedure [Proc_InsertProduct] ( @productname varchar(50) = null, @price money = null } AS if exists(select productname from tblProducts where productname = @productname) return 55555 else insert into tblproducts (productname, price) values (@productname, @price) return @@errorA simple sample stored procedure that checks to see if the product already exists