Hi All,
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?
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)
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.