SQL Try catch purpose unclear

Question

Let's suppose I want to inform the application about what happened / returned the SQL server. Let's have this code block:

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
     SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;
GO

and Let's have this code block:

SELECT 1/0;

My question is: Both return the division by zero error. What I don't understand clearly is that why I should surround it with the try catch clausule when I got that error in both cases ? Isn't it true that this error will be in both cases propagated to the client application ?

Answer 1

No, by executing Select 1/0 in a TRY/CATCH block the select statement returns nothing and the select statement in the catch block displays the error details gracefully. The query completes successfully - no errors are thrown.

If you run Select 1/0 on it's own the query does not complete successfully - it bombs out with an error.

Using a catch block within SQL gives you the chance to do something about it there and then not just let the error bubble up to the application.

The only reason you see the error details is because you are selecting them. If there was no code within the Catch block you wouldn't see any error information.

Answer 2

Both return the division by zero error.

Yes, but using different return paths.

The difference is that in the first example, you are anticipating the error and dealing with it in some way. The error enters the application as a regular result - it is not propagated via the error handling mechanism. In fact, if the application doesn't look specifically as the shape of the result, then it may be unaware that an error has occurred.

In the second instance, the error will propagate to your application typically via an error reporting mechanism, such as an exception. This will abort the operation. How big an impact this has will depend upon the application's exception handling. Maybe it will abort just the current operation, or the entire app may fail, depending upon the app's design and tolerance to exceptions.

You choose what makes sense for your application. Can the app meaningfully handle the error - if so, propagate the error (2nd example), or is it best handled in the query (1st example), with errors being "smoothed over" by returning default results, such as an empty rowset.

Answer 3

Using the first method, you wont get the error from SQL Server directly

The second method may stop the execution of the statements that follow it

So it is better you catch it in advance

Answer 4

Yes, the only reason for a Try Catch, (as in ordinary code) is if you can "Handle" the error, i.e., you can correct for the error and successfully complete whatever function the procedure was tasked to do, or, if want to do something with the error before returning it to the client (like modify the message, or store it in an error log table or send someone an email, etc. (althought i'd prefer to do most of those things from the DAL layer )

Technically, however, the catch clause is not returning an error. it is just returning a resultset with error information. This is very different, as it will not cause an exception in client code. This is why your conclusion is correct, ou should just let the original error propagate directly back to the client code.

As you have written it, no error will be returned to the client. As in ordinary code, if you do not handle (correct for) the error in a catch clause, you should always rethrow it (in sql that means Raiserror function) in a catch clause. What you have done above, in general is bad, the client code may or may not have any capability to properly deal with a completely different recordset (one with error info) from what it was expecting. Some calls (like Inserts updates or deletes) may not be expecting or looking for a returned recordset at all... Instead, if you want or need to do something with the error in the procedure before returning it to the client, use Raiserror() function

BEGIN TRY 
    -- Generate divide-by-zero error. 
    SELECT 1/0; 
END TRY 
BEGIN CATCH 
     -- Other code to do logging, whatever ... 
     Raiserror(ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_STATE() )
END CATCH; 

Answer 5

Try Catch is not as useful when all you have in the try portion is a select. However if you have a transaction with multiple steps, the catch block is used to roll all the steps back and possibly to record details about what caused the problem in a log. But the most important part is the rollback to ensure data integrity.

If you are creating dynamic SQl within the Try block, it is also helpful to log the dynamic SQl variable that failed and any parameters passed in. This can help resolve some hard-to-catch, "we don't have any idea what the user actually did to cause the problem" errors.