March 9, 2012 at 4:01 am
hi,
I have wrote a stored proc that is working fine. Now developer will use that in code. I have set any return value in stored proc. Now will there some default status or technique exist that can verify at code level whether stored porc executed successfully or not ?
e.g is there some default return type for proc exits etc
March 15, 2012 at 11:16 am
There are multiple ways to report errors from stored procedures. One way it so set the return code, which is always of type INT. The number can mean whatever you want, but it has to be a contract with the caller so they know what to expect in terms of which return codes mean what outcome. The most generic numbers are 0 = success, and anything but 0 = unsuccessful, however as I said, you may want to assign a meaning to a return code of 1, or -1, or any other INT.
In my opinion using the return code is an antique way to determine success or failure. Another popular way of showing the caller that an exception occurred is to raise an error using the RAISERROR function. This signals the caller that a problem occurred, and provides information about the exception like the line # it occurred on, the severity, the state, the object it occurred in and the error message.
Here is a great article to get you started with error handling in SQL Server. It was written for SQL 2005 originally but is still relevant for SQL 2008 and SQL 2008 R2. Error Handling in SQL 2005 and Later
Everything stated can be implemented ni SQL 2012, however your options in the CATCH block have changed a bit in SQL 2012 with the advent of THROW.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2012 at 8:18 am
Return values are rarely used to determine the status of a stored procedures execution. Normally you will either let the error bubble up to the client or handle the error in the SP and send back a custom error message using RAISERROR or in 2012 THROW.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2012 at 8:31 am
Jack Corbett (3/16/2012)
Return values are rarely used to determine the status of a stored procedures execution. Normally you will either let the error bubble up to the client or handle the error in the SP and send back a custom error message using RAISERROR or in 2012 THROW.
Agreed, for new development on modern systems. We still have to know the history and technique however because it will be around for a while longer. Case in point: xp_CmdShell.
Also, some middleware cannot handle RAISERROR-type feedback and thus requires the use of return codes to report execution outcome. An example of this came up on a job where I needed to provide a proc for an old IVR to call and it could only make a decision based on a return code of 0, 1 or 2.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2012 at 9:19 am
thank you opc and Jack.
I am using return code because I will not throw error to client.
My application will hit stored porc and proc will do some processing (will execute more than one proc, one by one) and will insert execution status in a table.
In this way I can see at any stage what proc is failed to make necessary actions.
March 16, 2012 at 2:18 pm
I typically code stored procedures using an output parameter that is also a return code. When the procedure executes to completion, the @status variable will have a return value of 1, a logical exit would return 0, and an abnormal exit would return a value < 0. Often times a runtime error doesn't accurately pinpoint where in T-SQL the error occurred or why. But by changing the value of @status at various code blocks, this return code can not only indicate success / fail but also where in code the failure occurred. I still raise the runtime error, the @status code just makes exception handling in the calling application more efficient, because they can trap for specific return codes.
create procedure MyProc
(
@status int output = -1
)
as
IF (SELECT ...) = 0
BEGIN
select @status = 0;
RETURN @status;
END;
select @status = -10;
UPDATE ...
...
select @status = -20;
INSERT ...
...
select @status = 1;
RETURN @status;
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply