title: Is returning an error code value from stored procedure good practice?
body: <p>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.</p>
  
  <p>To illustrate:</p>
  
  <pre><code>-------------------    
  --INSERT sp example
  -------------------
   INSERT INTO dbo.SomeTable(..) VALUES(...)
  
   if @@error &lt;&gt; 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 &lt;&gt; 0     
    return -1
  if @updcount &lt; 1 
    return -2
  return 0    
  
  -------------------
  --DELETE sp example
  -------------------
  delete from dbo.SomeTable where ...
  
  if @@error &lt;&gt; 0
    return -1
  return 0
  </code></pre>
  
  <p>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 :).</p>
  
  <p>So my question is - can you see any benefit in this practice, or is this classical cargo cult programming example ? </p>