• quote:


    Generally, most things can be trapped but the more error trapping is done at the SQL end the bigger the performance hit.

    If there are going to be multiple apps hitting a database then I put more error trapping in the database and middle tier. If there are very few apps then I expect the app to take the burden of the error trapping.


    Very true! But in a high transaction environment this is not always doable. I guess I've got a general gripe at the way SQL Server/T-SQL handles errors, because for the errors we've been talking about (and without using the trapping you've described), as stored procedure developers, we have to say 'we can trap some errors but not all..' given that, it splits the error trapping duties between the stored proc and the client/middle tier, when what we need at the stored proc level is a comprehensive trapping mechanism. The problem is even more complicated when you add in transaction handling as it's not always clear what was rolled back but that's another thread...