3

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.

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?

1

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

1
  • 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. – Chris Chilvers Nov 26 '15 at 16:27
-3

Take a look at SET XACT_ABORT option.

1
  • That will work in some cases, but can't be used in conjunction with save points (i.e. SAVE TRANSACTION foo; ...; ROLLBACK TRANSACTION foo) – Chris Chilvers Nov 26 '15 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.