Return value from a stored proc on error

Question

Hi there,

I have an sp in SQL Server that when errors returns -4

what does -4 mean? Is there a table somewhere explaning what the possible return values are?

Answer 1

There is no standard for return codes. You'll have to find out what -4 means in that particular stored procedure. In fact, not all return codes are errors.

---

EDIT: counter-example

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RetValTest] 
AS
BEGIN
 select 1/0;
END

GO

Execution:

DECLARE @return_value int

EXEC @return_value = [dbo].[RetValTest]

SELECT 'Return Value' = @return_value

GO

Result:

Msg 8134, Level 16, State 1, Procedure RetValTest, Line 9
Divide by zero error encountered.

This is with SQL Server 2008.

---

Some research suggests that this behavior may be left over from SQL Server 6.0. If that is the case, then you can decide for yourself how reliable it's likely to be, given that they stopped documenting it (and stopped guaranteeing its accuracy) so long ago.

---

My "research" is due, with thanks, to SQL Server MVP Tibor Karaszi. His source is Books Online for SQL Server 6.5. Under ""Control-Of-Flow Language", RETURN", he found

"SQL Server reserves 0 to indicate a successful return and reserves negative values from - 1 through - 99 to indicate different reasons for failure. If no user-defined return value is provided, the SQL Server value is used. User-defined return status values should not conflict with those reserved by SQL Server. The values 0 through -14 are currently in use.

Answer 2

There must be some standard

For example

declare @RetVal int
EXEC @RetVal = stpTest
select @RetVal

where stpTest is "SELECT 1/0" returns -6.

-6 must mean something!

Answer 3

for system messages, you can use the sysmessages table, for custom messages, you need to follow @John Saunders' advice.

Answer 4

The sp that returned -4 only has UPDATE and SELECT INTO statements in it.

At no point does it do 'SELECT -4' so how can I "find out what -4 means in that particular stored procedure"?

Also, if there is no standard then why does a divide by zero error always return -6?

Answer 5

Hi John

If you have an sp that does not return anything i.e. it doesn't have any select statements in it and you do:
declare @RetVal int
EXEC @RetVal = yourSPName

Then @RetVal will have a value of 0.

If there is an error then @RetVal will be a value other then zero, for example if the only thing your sp does is "SELECT 1/0" then @RetVal will be -6.

Try it and see

My question is what do these return values mean? They must have some logical meaning!

Answer 6

I'm not sure there's a way to know this without asking the SQL Server devs. It's something lower level than your stored proc evaluating what you're sending it and generating that return code. Unless you've specifically got a RETURN -4 in your code, it's coming from the SQL parser probably.

Answer 7

I think the question should be edited to ask -

What are the default return values if you dont have a RETURN statement in your stored proc?

Something I did find was from this link www.redware.com/handbooks/sql_server_handbook/sql_server_stored_procedures.html

SQL Server will default the return value to zero. The returned values are typically used to return a status flag from the stored procedure with a non-zero value usually indicating failure during processing.

Returned values are difficult to access using ODBC their use is recommended only to return a success or failure of the stored procedure when communicating with other stored procedures.

From this link - sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_&_Return_Values

The return values -99 through 0 are reserved for SQL Server internal use. You can create your own parameters that can be passed back to the calling program.

Also another link (I guess) from @Erland Sommarskog www.sommarskog.se/error-handling-I.html

Return Values from Stored Procedures

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.

Whether these negative numbers have any meaning, is a bit difficult to tell. It used to be the case, that the return values -1 to -99 were reserved for system-generated return values, and Books Online for earlier versions of SQL Server specified meanings for values -1 to -14. However, Books Online for SQL 2000 is silent on any such reservations, and does not explain what -1 to -14 would mean.

With some occasional exception, the system stored procedures that Microsoft ships with SQL Server return 0 to indicate success and any non-zero value indicates failure.

Getting Error Information

Also if you need to find what the error is (rather than what -6 means) you could try putting your sql into a try catch, ie.

begin try

    select 1/0 as 'an error'

end try

begin catch

    select ERROR_NUMBER() as 'ERROR_NUMBER', 
           ERROR_SEVERITY() as 'ERROR_SEVERITY',
           ERROR_STATE() as 'ERROR_STATE',
           LEFT(ERROR_PROCEDURE(),50) as 'ERROR_PROCEDURE',
           ERROR_LINE() as 'ERROR_LINE' , 
           LEFT(ERROR_MESSAGE(),40) as 'ERROR_MESSAGE'    
end catch

Answer 8

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.