Is returning an error code value from stored procedure good practice?

Question

In our company, we're using four types of simple stored procedures (insert, update, delete, select); database group persist on a rule that every single stored procedure should contain "error code" in return value for update/delete/insert stored procedure types.

To illustrate:

-------------------    
--INSERT sp example
-------------------
 INSERT INTO dbo.SomeTable(..) VALUES(...)

 if @@error <> 0
   return -1
 return 0

-------------------
--UPDATE sp example
-------------------
declare
@errsql int
@updcount int

update dbo.SomeTable set foo = @bar

select @errsql = @@error, @updcount = @@rowcount
if @errSQL <> 0     
  return -1
if @updcount < 1 
  return -2
return 0    

-------------------
--DELETE sp example
-------------------
delete from dbo.SomeTable where ...

if @@error <> 0
  return -1
return 0

This rule dates back to ages when we're using old ASP/VB6.0; however for a long age our platform is pure .NET and error on SQL side (such as primary key violation/unique index violation/etc.) is transferred to application as .NET exception, so following this pattern seems like a cargo cult programming (I can see checking updates rows count can be useful in some scenarios, still that should be driven by application developers, not by database group - in the end you have to check return code in the app to have any effect :).

So my question is - can you see any benefit in this practice, or is this classical cargo cult programming example ?

Answer 1

If you are using exceptions in the rest of your project, I'd say exceptions should be the way to go. Notice you have the RAISEERROR (http://msdn.microsoft.com/en-us/library/ms178592.aspx) SQL clause so you can raise exceptions within those stored procedures.

I believe the severity must be over 16 on RAISEERROR for it to be raised as an exception (you may want to check http://msdn.microsoft.com/en-us/library/ms164086.aspx). Exceptions should rise for any error that would work on a TRY/CATCH SQL block.