SET XACT_ABORT ON 先填上
使用 try catch 参考 http://msdn.microsoft.com/zh-cn/library/ms175976.aspx最后,真的不建议这样写事务 ,之前碰到-1 +1 事务@@不同的问题。 真是不好处理 。参考 @@transcount
使用 try catch 参考 http://msdn.microsoft.com/zh-cn/library/ms175976.aspx最后,真的不建议这样写事务 ,之前碰到-1 +1 事务@@不同的问题。 真是不好处理 。参考 @@transcount
解决方案 »
- 如何让一张表里面的两个数据加一个相同的随机数
- 批量更新
- 日期数据类型
- 这条sql语句编译出现错误,解决马上给分!急!!!
- 我用VB程序动态的创建一个SQL数据库。。。在线等!
- http://bernice.go.zccn.net 我的网站去捧捧场。^o^ ^o^
- 十万火急,此题关乎我的命运啊....谢谢!!!!!1
- 大神门,你们的代码好酷炫~
- sql server 2005 如何自动备份数据库,要求时间间隔是 “每年”,在维护向导里面只有最多每月备份一次,求教高手
- 求助,数据库附加后,里面表是空的!
- SQL问题求助,急.......................................................................
- sql存储过程 参数有默认值怎么传
也就不用说回滚了纯手工写了一个测试的例子,看效果就行了
create table t1(id int,name varchar(10))create table t2(id int,name varchar(10))create proc p_innerProcTest
as
beginbegin try
begin tran
insert into t1 values(1,'A');
if exists(select 1 from t1 where id=1)
begin tran
insert into t1 values(1,'ABCDEFGHIJKLMN');--执行失败,
commit
commit
end trybegin catch
rollback;
end catch
end
EXEC p_innerProcTestSELECT * FROM t1select * from t2
意识是为了表达事务嵌套的异常的处理方式
我明白你的意思是存储过程调用存储过程,其实处理起来大同小异
你可以参考下面文章中总结的
http://www.cnblogs.com/rush/archive/2011/12/11/2284262.html
**********************************************************************
另外是题外话,可以无视
对事事务的处理,老老实实在脚本中做异常处理
有人喜欢使用SET XACT_ABORT ON声明的方式,从而忽略了异常处理,也可以达到事务的目的,但是不建议这么做
One way to make your error handling simpler is to run with SET XACT_ABORT ON. With this setting, most errors abort the batch. This may give you the idea that you don't need any error handling at all in your stored procedures, but not so fast! I said most errors, not all errors.Even if XACT_ABORT is ON, as a minimum you must check for errors when calling stored procedures, and when you invoke dynamic SQL. This is because XACT_ABORT does not affect compilation errors, and compilation errors are typically those that cause SQL Server to abandon execution of a procedure and return control to the caller. Nor will the batch be aborted because of a RAISERROR, so if you detect an error condition, you still need to return a non-zero value to the caller, that has to check for it. Also, when XACT_ABORT is ON, error 266, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing, does not abort the batch. This is not documented in Books Online, and it makes me a little nervous that there might be more errors that SET XACT_ABORT ON does not affect. In any case, I would suggest that if you use SET XACT_ABORT ON, you should use it consistently, preferably submitting the command from the client directly on connection. What you should not do, is to use it sometimes and sometimes not. Particularly it is bad, if you as an individual programmer as your private standard insert a SET XACT_ABORT ON in the procedures you write, while your colleagues do not. Say that another programmer calls your code. He might have some error-handling code where he logs the error in a table. While SQL Server may abort the batch for some errors, sufficiently many errors let execution continue to make such a scheme worthwhile. As long as not any joker starts to play games with SET XACT_ABORT ON, that is. (Note: there are some situations with distributed queries where SET XACT_ABORT ON is required for them to work. This is the exception to the rule that you should not use XACT_ABORT ON sometimes.)