Is there a way to make SQL Server stored procedures automatically exit on error?

Question

Consider the following stored procedure:

CREATE PROCEDURE [dbo].[TestError]
    @Input int
AS
BEGIN
    DECLARE @Test int = 1 / 0;

    INSERT TestTable (Number) 
    VALUES (@Input);
END

And calling it:

EXEC TestError 123;

When I execute this stored procedure, the TestTable table still gets populated, despite the divide by zero error.

Can stored procedures be set to automatically exit on error, so that subsequent statements don't get executed?

Answer 1

add set xact_abort on; to the beginning of your procedures.

create procedure [dbo].[TestError] @Input int as
begin
set xact_abort, nocount on;
    declare @Test int = 1 / 0;
    insert TestTable (Number) values (@Input);
end

Why you should always include set xact_abort, nocount on; - Erland Sommarskog

This turns on two session options that are off by default for legacy reasons, but experience has proven that best practice is to always have them on. The default behaviour in SQL Server when there is no surrounding TRY-CATCH is that some errors abort execution and roll back any open transaction, whereas with other errors execution continues on the next statement. When you activate XACT_ABORT ON, almost all errors have the same effect: any open transaction is rolled back and execution is aborted. There are a few exceptions of which the most prominent is the RAISERROR statement. - Erland Sommarskog