テーブルにカラムを追加し、トランザクション内でそれを更新する

質問

MS SQL サーバーで実行されるスクリプトを作成しています。このスクリプトは複数のステートメントを実行し、トランザクションである必要があり、ステートメントの 1 つが失敗すると、全体の実行が停止し、すべての変更がロールバックされます。

ALTER TABLE ステートメントを発行してテーブルに列を追加し、新しく追加された列を更新する際に、このトランザクション モデルを作成するのに苦労しています。新しく追加された列にすぐにアクセスするために、私は GO コマンドを使用して ALTER TABLE ステートメントを実行し、それから UPDATE ステートメントを呼び出します。私が直面している問題は、IF文の内部でGOコマンドを発行することができないことです。IF文は、私のトランザクション・モデルの中で重要です。これは、私が実行しようとしているスクリプトのサンプルコードです。GOコマンドを発行すると、@errorCode変数が破棄され、使用する前にコードで宣言する必要があることに注意してください（これは以下のコードにはありません）。

BEGIN TRANSACTION

DECLARE @errorCode INT
SET @errorCode = @@ERROR

-- **********************************
-- * Settings
-- **********************************
IF @errorCode = 0
BEGIN
 BEGIN TRY
  ALTER TABLE Color ADD [CodeID] [uniqueidentifier] NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
  GO
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

IF @errorCode = 0
BEGIN
 BEGIN TRY
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

-- **********************************
-- * Check @errorCode to issue a COMMIT or a ROLLBACK
-- **********************************
IF @errorCode = 0
BEGIN
 COMMIT
 PRINT 'Success'
END
ELSE 
BEGIN
 ROLLBACK
 PRINT 'Failure'
END

そこで私が知りたいのは、ALTER TABLE ステートメントを発行してカラムを追加し、そのカラムを更新する、この問題を回避する方法です。すべてトランザクション単位として実行されるスクリプト内で行われます。

どのように解決するのですか?

GO は T-SQL コマンドではありません。バッチデリミターです。クライアントツール（SSM、sqlcmd、osqlなど）は、これを使用して効果的に カット を効果的に切断し、個々のバッチをサーバーに送ります。したがって、明らかに IF 内で GO を使用することはできませんし、変数がバッチ間でスコープをまたぐことを期待することもできません。

また、IFの中で XACT_STATE() をチェックして、トランザクションが絶望的でないことを確認する必要があります。

IDにGUIDを使うのは、少なくとも常に怪しい。

NOT NULL制約を使用し、デフォルトの'guid'を提供するような '{00000000-0000-0000-0000-000000000000}' も正しいとは言えません。

更新しました。

• ALTERとUPDATEを2つのバッチに分離します。
• エラー時にスクリプトを中断するために sqlcmd 拡張を使用します。これは以下のようにサポートされています。 SSMS で sqlcmd モードがオンのとき でサポートされており、クライアントライブラリでもサポートするのは簡単です。 dbutilsqlcmd .
• 使用 XACT_ABORT を使うと、強制的にエラーを発生させてバッチを中断させることができます。これはメンテナンススクリプト（スキーマの変更）で頻繁に使用されます。ストアドプロシージャやアプリケーションロジックのスクリプトでは、一般的にTRY-CATCHブロックを代わりに使用しますが、適切な注意が必要です。 例外処理とネストされたトランザクション .

スクリプトの例です。

:on error exit

set xact_abort on;
go

begin transaction;
go

if columnproperty(object_id('Code'), 'ColorId', 'AllowsNull') is null
begin
    alter table Code add ColorId uniqueidentifier null;
end
go

update Code 
  set ColorId = '...'
  where ...
go

commit;
go

成功したスクリプトだけが COMMIT . エラーが発生すると、スクリプトは中断され、ロールバックされます。

私が使ったのは COLUMNPROPERTY を使用してカラムの存在を確認しますが、代わりに任意の方法を使用できます (例: ルックアップ sys.columns ).