If your store procedure fails before hitting a RETURN statement then the return value will be zero. This is the same value that you have used if your stored proc suceeds therefore your stored procedure needs to return a non-zero value to indicate success.