TRY/CATCH block vs SQL checks

Shortly, I am wondering which of the following is the better practice:

• to encapsulate my code in TRY/CATCH block and display the error message
• to write own checks and display custom error messages

As I have read the TRY/CATCH block is not handling all types of errors. In my situation this is not an issue.

I am building dynamic SQL that executes specific store procedure. I can make my own checks like:

• is the procedure that is going to be executing exists
• are all parameters supplied
• can be all values converted properly

or just to encapsulate the statement like this:

BEGIN TRY           
    EXEC sp_executesql @DynamicSQLStatement
    SET @Status = 'OK'
END TRY
BEGIN CATCH
    SET @Status =  ERROR_MESSAGE()
END CATCH

Is there any difference if I make the checks on my own (for example performance difference) or I should leave this work to the server?

The reason I am asking is because in some languages (like JavaScript) the use of try/catch blocks is known as bad practice.

Answer

Typically it is going to be better to check for these things yourself than letting SQL Server catch an exception. Exception handling is not cheap, as I demonstrate in these posts:

• Checking for potential constraint violations before entering SQL Server TRY and CATCH logic

• Performance impact of different error handling techniques

Depending on the schema, volume, concurrency, and the frequency of failures you expect, your tipping point may be different, so at very high scale this may not always be the absolute most efficient way. But in addition to the fact that in most cases you’re better off, writing your own error handling and prevention – while it takes time – allows you to fully understand all of your error conditions.

That all said, you should still have TRY / CATCH as a failsafe, because there can always be exceptions you didn’t predict, and having it blow up gracefully is a lot better than throwing shrapnel everywhere.