Enable TRY style error handling

Question

Is there a SET command or similar I can enable to get the same error handling as a try/catch block? Specifically halting the script/procedure on the first error.

The only way I've currently found so far is to wrap the whole query in a try/catch such as

BEGIN TRY
 -- procedure body goes here
END TRY
BEGIN CATCH
 THROW;
END CATCH

While this hack does work to enable the correct error handling behaviour it just feels wrong to have a catch block that just re-throws the error.

In this particular case there's nothing more I need to do with the error other than halt the script and throw the error back out to the script runner.

Answer 1

It depends on whether you want to abort the entire batch. If you want to entirely abort the batch and since you don't want to go ahead with try catch, XACT_Abort will do the trick for you:

create table test1
(id int);
create proc usp_test
as
begin
 set nocount on
 set xact_abort on
 insert into test1 
 select 1
 select 1/0
 select 1
end

Now try to execute the procedure and also you will see Select does not succeded

exec usp_test
select * from test1

Further Throw, raiserror also gives me the same behaviour, but I always tend to use set Xact_abort on if I want my transaction to rollback entirely even in case of timeouts. Further MSDN page has more info:

The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR.

Below link gives you more idea on advantages of using xact_abort: What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?

Answer 2

In my opinion you should use TRY/CATCH every time when you have update, insert or delete statements. This has saved me a lot of headaches. Check this -- 1

• Chris Chilvers: I want the try/catch behaviour but without having to write a catch block that's only purpose is to re-throw the error. In this case the try/catch block is just a hack to ensure the try/catch behaviour of halting the script and propagating the error to the caller.

Answer 3

Take a look at SET XACT_ABORT option.

• Chris Chilvers: That will work in some cases, but can't be used in conjunction with save points (i.e. SAVE TRANSACTION foo; ...; ROLLBACK TRANSACTION foo)