sql-server - 有没有办法让 SQL Server 存储过程在出错时自动退出？

Question

考虑以下存储过程:

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

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

并调用它:

EXEC TestError 123;

当我执行这个存储过程时，TestTable 表仍然被填充，尽管出现了被零除 错误。

是否可以将存储过程设置为出错时自动退出，以便不执行后续语句？

Answer 1

将 set xact_abort on; 添加到程序的开头。

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