---- 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