TSQL - Return value from sp when sp does not conta

问题:

I have a stored procedure that is called by another stored procedure

ALTER PROCEDURE [dbo].[usp_Test] 
AS
begin
declare @errorCode int
declare @lastIdentity int
select @errorCode = @@ERROR
    if @errorCode=0
    begin
        update Vehicle  set model='1996----------'
            where Make='MERC'
        select @errorCode = @@ERROR
        select @lastIdentity = @@IDENTITY
    end 

print 'usp_test lastIdentity='  + convert(varchar(10), isnull(@lastIdentity,0)) 
print 'usp_test errorCode=' + convert(varchar(10), @errorCode) 

end

If I call the stored procedure like this

declare @RetVal int

exec @RetVal=usp_Test

print 'return value is ' + convert(varchar(10), @RetVal)

I get the following messages

Msg 8152, Level 16, State 14, Procedure usp_Test, Line 14 String or binary data would be truncated. The statement has been terminated. usp_test lastIdentity=0 usp_test errorCode=8152 return value is -6

By adding RETURN 0 at the end and RETURN @errorCode after the select @errorCode... I will have a nice clean way of returning the error and subsequently handle it. I am surprise that without any RETURN's I get a return value of -6. Can anyone explain why this is the case?

回答1:

copied from this answer

If you have a RETURN statement with an explicit return value, that is of course the return value.

But if there is no RETURN statement, but an error occurs during execution, the return value is 10 minus the severity level of the error. Division by zero is level 16, thus the return value is -6. Permissions errors are typical level 14, thus the return value is -4.

As you may guess this is not terribly useful, but this: 0 is success, and everything else is an error.

回答2:

The standard return value when you did not specify anything and the procedure executed correctly is 1.

When the procedure returns an error and you did not specify a return value the default returned is 10 - errorlevel with a maximum value of 0.

for example:

    CREATE PROCEDURE p_error16
    AS 
    RAISERROR('error', 16, 1)
    GO

will return -6 where the following proc will return -7:

    CREATE PROCEDURE p_error17
    AS 
    RAISERROR('error', 17, 1)
    GO

if the errorlevel goes under 10 the value returned is 0:

    CREATE PROCEDURE p_error5
    AS 
    RAISERROR('error', 5, 1)
    GO

回答3:

The following link contains the answer I was looking for: http://www.sommarskog.se/error-handling-I.html#returnvalue

This is an extract. "All stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0. "