先创建一个测试表:
-- 创建测试表格 CREATE TABLE table_test ( [id] [int] NOT NULL PRIMARY KEY IDENTITY(1, 1), [name] [varchar] (50) NULL, ) GO
创建存储过程:
-- 测试用的存储过程 CREATE PROCEDURE proc_test @name AS VARCHAR(60) AS BEGIN INSERT INTO dbo.table_test(name) VALUES(@name) END GO
测试一个事务中执行多次存储过程:
-- 在一个事务中执行两次存储过程 BEGIN TRANSACTION --执行存储过程 EXECUTE dbo.proc_test @name='张三'; EXECUTE dbo.proc_test @name='李四'; EXECUTE dbo.proc_test @name='王五'; --提交存储过程 COMMIT TRANSACTION GO
执行结果:
关于存储过程中执行出错后,会继续执行后续的脚本。测试:
-- 在一个事务中执行两次存储过程 BEGIN TRANSACTION --执行存储过程 EXECUTE dbo.proc_test @name='张三'; -- 长度超过表格的50,超过存储过程变量的60 EXECUTE dbo.proc_test @name='李四asdfasdfasdfasdfasdfasdfasfasdfasdfasdfasdfasdfasdfasfasdfasdfasdfasdfasdfasdfasf'; EXECUTE dbo.proc_test @name='王五'; --提交存储过程 COMMIT TRANSACTION GO
执行结果:
可以看到参数超过存储过程的变量长度,存储过程就报错了。但后续的脚本继续执行完成了。
我们应该优化语句:
-- 在一个事务中执行两次存储过程 BEGIN TRANSACTION -- 通过变量来记录错误 【注意】@@ERROR是错误编码而不是错误数 DECLARE @sumError INT = 0; --执行存储过程 EXECUTE dbo.proc_test @name='张三'; SET @sumError = @sumError+@@ERROR -- 长度超过表格的50,超过存储过程变量的60 EXECUTE dbo.proc_test @name='李四asdfasdfasdfasdfasdfasdfasfasdfasdfasdfasdfasdfasdfasfasdfasdfasdfasdfasdfasdfasf'; SET @sumError = @sumError+@@ERROR EXECUTE dbo.proc_test @name='王五'; SET @sumError = @sumError+@@ERROR IF (@sumError <> 0) BEGIN --提交存储过程 ROLLBACK TRANSACTION END ELSE BEGIN --提交存储过程 COMMIT TRANSACTION END GO
优化方案二:
--用 TRY CATCH BEGIN TRY -- 在一个事务中执行两次存储过程 BEGIN TRANSACTION --执行存储过程 EXECUTE dbo.proc_test @name='张三'; -- 长度超过表格的50,超过存储过程变量的60 EXECUTE dbo.proc_test @name='李四asdfasdfasdfasdfasdfasdfasfasdfasdfasdfasdfasdfasdfasfasdfasdfasdfasdfasdfasdfasf'; EXECUTE dbo.proc_test @name='王五'; --提交存储过程 COMMIT TRANSACTION END TRY BEGIN CATCH --提交存储过程 ROLLBACK TRANSACTION END CATCH GO
参考: