Sql语法高级应用之七:如何在存储过程中使用事务
普通事物:
USE Wot_Inventory; GO BEGIN TRANSACTION tr; DECLARE @error INT; SET @error = 0; SELECT * FROM Wot_Inventory.dbo.Logistics; SET @error = @error + @@ERROR; SELECT 1 / 0; SET @error = @error + @@ERROR; SELECT * FROM Wot_Inventory.dbo.Invoice; SET @error = @error + @@ERROR; PRINT 'i have executed!'; SET @error = @error + @@ERROR; PRINT '@@error:' + CAST(@error AS VARCHAR(50)); IF (@error <> 0) BEGIN PRINT 'executed failed'; ROLLBACK TRANSACTION tr; END; ELSE BEGIN PRINT 'executed success'; COMMIT TRANSACTION tr; END;
结合TRY...CATCH的SQL事物
USE Wot_Inventory; GO BEGIN TRY BEGIN TRANSACTION tr; UPDATE dbo.Logistics SET EngFlag = 1 WHERE LogisticCode = '620752867926'; SELECT 1 / 0; PRINT 'i have executed!'; SELECT * FROM dbo.Logistics; PRINT 'executed success'; COMMIT TRANSACTION tr; END TRY BEGIN CATCH PRINT 'executed failed'; ROLLBACK TRANSACTION tr; END CATCH;
PS:欢迎扫描下方二维码或点击链接,加入QQ群