Return value from a stored proc on error

Question

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

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

---

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!

---

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?

---

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 1

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.