Adding Exceptional Handling is a good practise, but I have a doubt, In our database we have 10 SPs which are accessed by our data access layer, there is not much logic and the queries are also not big.The purpose of keeping the queries in SPs was to allow paged data to be easily passed to the presentation layer. So, my question is, should error handling be added to the SPs in this scenario when they do not include large complex queries?
Add a comment
|
2 Answers
yes.
TRY/CATCH will trap errors that will otherwise stop the stored proc running, for example, datatype conversion errors. It will also allow you to log the error in the CATCH block, for example, or retry (eg have proc call itself)
Comments
Most paging queries should be a single SELECT statement, or maybe two if you're optimizing with SET ROWCOUNT/LIMIT. I don't think that there's any error that could possibly occur in this instance that you would be able to handle from within the database.
Generally the main reason for implementing error handling at the database level is to combine it with transactional logic so that you roll back the right changes at the right time when necessary. If you aren't actually executing any DML, I don't really see a good reason to write special error-handling code.
Of course you haven't shown us the queries here either... if you are writing some kind of multi-statement DML in there, then putting aside the fact that it's a weird thing to be doing in a paging query, you would want to have some sort of error-handling. It's not so much the size of the query that matters, but rather what the query does; if you're making modifications to the data then you should consider implementing error handling, but if not, then I don't see any good reason for it.
2 Comments
gbn
I would say that you use a template for every stored proc, whether making data changes or not. In the future, what if you want to re-use this select proc inside an update proc? What if a count/assign overflows a local variable in the select? It's just as easy to include it as exclude it, no?
Aaronaught
@gbn: Maybe it's just because I despise the SSMS template system (as compared to, for example, VS code snippets), but that seems like unnecessary work to me. Most of us don't obsessively write try-catch blocks around everything we do in app code, so I don't see why it ought to be done for a sproc with a single
SELECT statement, and I can't think of any case where I've reused a SELECT procedure inside another procedure. I'd also consider it an unlikely occurrence that a "reporting" procedure would be changed to make modifications; in the event that it does, add exception handling, simple.