---- Tran 事务 以及 Goto 的浅显应用

IF OBJECT_ID('tempdb..#tmptab','U')  IS NOT NULL DROP TABLE #tmptab

GO

CREATE TABLE #tmptab (id int IDENTITY(1,1),name VARCHAR(10),bankMoney MONEY)

GO

INSERT INTO #tmptab(name,bankMoney)

SELECT 'Jack',100

UNION

SELECT 'Frank',200

GO

DECLARE @tran_errorcount INT=0

BEGIN TRANSACTION bankTran

BEGIN TRY

--SELECT 1/0

UPDATE #tmptab SET bankMoney=bankMoney+100 WHERE name='Jack'

UPDATE #tmptab SET bankMoney=bankMoney-100 WHERE name='Frank'

END TRY

BEGIN CATCH

SET @tran_errorcount+=1

DECLARE @error_number INT =ERROR_NUMBER(),

		@error_msg VARCHAR(256)=ERROR_MESSAGE()

RAISERROR('Error Member is: %i and error message: %s',-1,-1,@error_number,@error_msg) WITH NOWAIT

END CATCH

PRINT @tran_errorcount

IF @tran_errorcount>0 GOTO error_process

ELSE GOTO success_process

success_process: RAISERROR('Transaction finished successfully! Congratulations!',-1,-1) WITH NOWAIT COMMIT TRAN GOTO end_process 

error_process: RAISERROR('Warning! Transaction has exception!',-1,-1) WITH NOWAIT  ROLLBACK TRANSACTION GOTO end_process

end_process:

SELECT * FROM #tmptab RETURN