Как установить "SET XACT_ABORT ON " в транзакции SQL Server?

Question

Я хочу установить SET XACT_ABORT ON в хранимой процедуре SQL Server 2008R2 с транзакцией, поэтому сделайте это в скрипте создания:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET XACT_ABORT ON
GO

CREATE PROCEDURE MyProc
AS
BEGIN TRAN  
    ... 
IF @@ERROR <> 0
BEGIN
    GOTO Done
END 
    ... 
IF @@ERROR <> 0
BEGIN
    GOTO Done
END 
COMMIT TRAN     
Done:
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRAN
END
GO

После успешного создания я проверяю транзакцию, нажимая опцию "Изменить" хранимой процедуры и в сгенерированном ALTER PROCEDURE сценарий я не вижу SET XACT_ABORT ON линия:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE MyProc
AS
BEGIN TRAN
...

Где я не прав или в чем прикол? Как правильно определить SET XACT_ABORT ON?

Answer 1

Вы обычно устанавливаете xact_abort как часть тела хранимой процедуры:

CREATE PROCEDURE MyProc
AS
SET XACT_ABORT ON
BEGIN TRAN  
....

Есть две "специальные" настройки, которые запоминаются из сеанса, который создал процедуру. Объяснение из MSDN:

Хранимые процедуры выполняются с настройками SET, указанными во время выполнения, за исключением SET ANSI_NULLS и SET QUOTED_IDENTIFIER. Хранимые процедуры, указывающие SET ANSI_NULLS или SET QUOTED_IDENTIFIER, используют настройку, указанную во время создания хранимой процедуры. Если используется внутри хранимой процедуры, любая настройка SET игнорируется.

Поэтому, когда вы создаете хранимую процедуру, SQL Server копирует параметр QUOTED_IDENTIFIER из соединения в определение процедуры. Цель состоит в том, чтобы кто-то другой с другим параметром QUOTED_IDENTIFIER по-прежнему получал поведение, намеченное автором процедуры.

То же самое не верно для XACT_ABORT,

Answer 2

Вы не упомянули, используете ли вы SQL Management Studio или нет, но если вы это сделаете и нажмете "Изменить" в существующей хранимой процедуре (я полагаю, это то, что вы сделали), то MS просто генерирует шаблонный сценарий на основе содержимое существующей хранимой процедуры.

Возможно, вы захотите определить свои хранимые процедуры в отдельном файле сценария, который выполняет и ALTER PROCEDURE, а также любые другие параметры, которые вы хотите вне sproc (например, SET XACT_ABORT ON). Таким образом, вы получаете больше контроля и можете просто выполнить скрипт для обновления sproc.