T-SQL, TRY-CATCH, TRANSACTION

t-sql_try_trans.sql

--# T-SQL同時使用TRY-CATCH與TRANSACTION語法
--# ref → https://msdn.microsoft.com/zh-tw/library/ms175976(v=sql.120).aspx

BEGIN TRANSACTION;
BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;

    --IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    --IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;

GO

t-sql_try_trans_2.sql

-- 
-- ref →　https://msdn.microsoft.com/zh-tw/library/ms175976(v=sql.120).aspx
-- ref →　https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql
-- ref →　https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql
-- ref →　https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql
--

BEGIN TRAN;
BEGIN TRY
-- === 交易碼:BEGIN ===

-- ERROR_NUMBER 需 50000 以上，ERROR_STATE 需 0~255。
THROW 50000, '測試用訊息', 0

-- === 交易碼:END ===
    --## SUCCESS
    COMMIT;
	PRINT 'SUCCESS';
    SELECT [ERROR_NUMBER] = @@ERROR
		  ,[ERROR_SEVERITY] = ERROR_SEVERITY()
		  ,[ERROR_STATE] = ERROR_STATE()
		  ,[ERROR_PROCEDURE] = ERROR_PROCEDURE()
		  ,[ERROR_LINE] = ERROR_LINE()
		  ,[ERROR_MESSAGE] = ERROR_MESSAGE();
END TRY
BEGIN CATCH
	--## FAIL
    ROLLBACK;
	PRINT 'EXCEPTION';
    SELECT [ERROR_NUMBER] = ERROR_NUMBER()
		  ,[ERROR_SEVERITY] = ERROR_SEVERITY()
		  ,[ERROR_STATE] = ERROR_STATE()
		  ,[ERROR_PROCEDURE] = ERROR_PROCEDURE()
		  ,[ERROR_LINE] = ERROR_LINE()
		  ,[ERROR_MESSAGE] = ERROR_MESSAGE();
END CATCH;

t-sql_try_trans_3.sql

-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[Your_Transaction] 
  @YOUR_PARAM1 VARCHAR
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
BEGIN TRY
-- ============ 交易碼:BEGIN ============

---- ERROR_NUMBER 需 50000 以上，ERROR_STATE 需 0~255。
THROW 50000, '測試用訊息', 0

-- ============ 交易碼:END ============
  --## SUCCESS
  COMMIT;
  PRINT 'SUCCESS';
END TRY
BEGIN CATCH
  --## FAIL
  ROLLBACK;
  PRINT 'EXCEPTION';
  THROW; -- 丟出去
END CATCH;
END