SQL Server error handling: exceptions and the database-client contract
- by gbn
We’re a team of SQL Servers database developers. Our clients are a mixed bag of C#/ASP.NET, C# and Java web services, Java/Unix services and some Excel.
Our client developers only use stored procedures that we provide and we expect that (where sensible, of course) they treat them like web service methods.
Some our client developers don’t like SQL exceptions. They understand them in their languages but they don’t appreciate that the SQL is limited in how we can communicate issues.
I don’t just mean SQL errors, such as trying to insert “bob” into a int column.
I also mean exceptions such as telling them that a reference value is wrong, or that data has already changed, or they can’t do this because his aggregate is not zero.
They’d don’t really have any concrete alternatives: they’ve mentioned that we should output parameters, but we assume an exception means “processing stopped/rolled back.
How do folks here handle the database-client contract? Either generally or where there is separation between the DB and client code monkeys.
Edits:
we use SQL Server 2005 TRY/CATCH exclusively
we log all errors after the rollback to an exception table already
we're concerned that some of our clients won't check output paramaters and assume everything is OK. We need errors flagged up for support to look at.
everything is an exception... the clients are expected to do some message parsing to separate information vs errors. To separate our exceptions from DB engine and calling errors, they should use the error number (ours are all 50,000 of course)
