SQLServer T-SQLのcommitとrollbackと例外処理

SQLServer T-SQLのcommitとrollbackと例外処理のサンプルです。

サンプル	commitとrollbackのサンプル
	独自の例外を作成する(RAISERROR)
	取得件数が0件のとき(@@ROWCOUNT)

commitとrollbackのサンプル

commitとrollbackのサンプルです。

CREATE PROCEDURE dbo.TEST1
AS
BEGIN TRY
	BEGIN TRANSACTION
	UPDATE dbo.employee
	  SET name = '安田', romaji = 'yasuda'
	  WHERE id = 2

	INSERT INTO dbo.employee (id,name,romaji)
	  VALUES (4,'竹田','takeda')

	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION
	PRINT ERROR_NUMBER()
	PRINT ERROR_MESSAGE()
END CATCH

5～7行目は、update文です。
9,10行目は、insert文です。
12行目は、commitです。上記2つのSQLでエラーが発生しない場合データを確定させます。
BEGIN TRYからEND TRYの間で例外が発生したときは、15行目以降のBEGIN CATCHからBEGIN ENDの間の処理が実行されます。

15行目以降は、エラーが発生したときに実行されます。
16行目は、rollbackです。上記2つのSQLの更新を行わず元に戻します。
例として、5行目のupdate文が成功し、9行目からのinsert文でエラーが発生した場合は、
5行目のupdate文の更新分は、更新前の状態に戻ります。

例外発生時のERROR_NUMBER()とERROR_MESSAGE()

17行目のERROR_NUMBER()は、数値が表示されます。
18行目のERROR_MESSAGE()は、エラーメッセージが表示されます。

以下は、メッセージです。

2627
制約 'PK__employee__3213E83F750459F3' の PRIMARY KEY 違反。オブジェクト 'dbo.employee' には重複するキーを挿入できません。重複するキーの値は (4) です。

上記プロシージャを2回実行するとキーの一意制約でエラーになります。

独自の例外を作成する(RAISERROR)

RAISERRORで独自の例外を作成するサンプルです。

CREATE PROCEDURE dbo.TEST1
AS
BEGIN TRY
	BEGIN TRANSACTION
	
	UPDATE dbo.employee
	  SET name = '安田', romaji = 'yasuda'
	  WHERE id = 2
	  
	RAISERROR('エラーです',16,1)

	INSERT INTO dbo.employee (id,name,romaji)
	  VALUES (4,'竹田','takeda')
	  
	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION
	PRINT ERROR_NUMBER()
	PRINT ERROR_MESSAGE()
END CATCH

10行目は、RAISERRORで強制的に例外を発生させています。
1つめの引数は50,000以降のエラー番号を設定することも可能です。
18行目は、10行目の例外をキャッチし、19行目のrollbackを実行します。

以下は、実行結果です。

https://docs.microsoft.com/ja-jp/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver15

取得件数が0件のとき(@@ROWCOUNT)

@@ROWCOUNTは、直前に実行したSQLの件数を取得します。

CREATE PROCEDURE dbo.TEST1
AS
BEGIN
	DECLARE @cnt1 INT
	SET @cnt1 = 0
	
	SELECT name FROM employee
	WHERE id = 1

	SET @cnt1 = @@ROWCOUNT
	
	IF @cnt1 = 0
		BEGIN
			PRINT '0件です'
		END
	ELSE
		BEGIN
			PRINT CAST(@cnt1 As varchar) + '件です'
		END
END

10行目は直前に実行したSQLの件数を変数にセットしています。