DECLARE @EMP_ID INT ,
@EMP_NAME VARCHAR(50)
DECLARE @EMP TABLE
(
EMP_ID INT ,
EMP_NAME NVARCHAR(50)
)
-- 模拟数据库中存在名为Yanan的数据
INSERT INTO @EMP
( EMP_ID, EMP_NAME )
VALUES ( 0, -- EMP_ID - int
N'Yanan' -- EMP_NAME - nvarchar(50)
)
SELECT * INTO #tmp FROM @EMP
IF EXISTS ( SELECT 1
FROM #tmp
WHERE EMP_NAME = 'Yanan' )
BEGIN
PRINT 0
RETURN
END
--事务要写在RETURN之后
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO #tmp
( EMP_ID, EMP_NAME )
VALUES ( 1, 'AAA' )
UPDATE #tmp
SET EMP_NAME = 'Yanan'
WHERE EMP_ID = 'AA'
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
PRINT 1
ROLLBACK
END
END CATCH
IF XACT_STATE() <> 0
BEGIN
PRINT 2
COMMIT TRAN
END
SELECT *
FROM #tmp
DROP TABLE #tmp
常用SQL整理 - TRY CATCH & TRAN
最新推荐文章于 2021-08-23 15:18:49 发布