SQLSERVER存储过程中的事物到底该怎么写？有图无真相，求解

[WYhack]

做天没弄懂，今天天继续，希望大家给点帮助，谢谢

实验目的：sqlserver的存储过程中事物的写法，以及对异常的处理


实验步骤：Table1，Table2，都有主键，在一个存储过程中往这个两个表中插数据，两条插入数据的sql分别放在事物中，有意使其异常


结果：我知道，存储过程中的事物（有多个），后面的一个发生异常后，前面执行成功的也不会提交，这里不在给出详细信息

但是关于返回值设置值的问题就不是这么回事了，还有一只没有解决的return到底该怎么用的问题


疑问：对于异常情况的处理的写法以及调试过程中发现@error变量的问题，还有就是网上看到把两个sql放到一个事物中，

BEGIN TRANSACTION sql1 sql2 COMMIT TRANSACTION

个人感觉粒度有点大，其实也是没问题的，但是想更进一步探索下


所以这里疑问就是return的用法问题，能不能这样return，还有就是存储过程中多个sql，卸载一块放在一个事物中还是分别放在一个事物中，异常了用return返回呢?

网上见到的另外一种写法就是也不太合适,因为即便是你if（@@error）成立，还是继续往下走，即便是后面sql出错，前面正常的sql最终不会执行成功，但这样感觉不大合适吧？

--事物开始

sql1

if(@@error>0)

--回滚
  

--事物提交 

--事物开始
  

sql2  

if(@@error>0)

--回滚

 --事物提交

图一：存储过程

图二：执行该存储过程，理所当然地成功并且插入了数据

图三：查询结果

图四：再次执行，肯定会有异常，调试下看看，奇怪的@@error变量问题，这里很清楚吧，2627，接着往下看

图五：明明走了if（@@error）分支，这时的@@error却变成了0，这是第一个疑问


图七：

返回值设置错误

if（@@error下面还有分支），既然走了分支1，分支2就不该走了吧，但是走完分支1后@@error变成了0，所以就走分支2，分支2中又根据@@error判断来给返回值赋值，

其结果就是一错再错啊



图八：最后的再次判断，因为@@error为0，判断当然无效了

图九：结果就提交了

图十：但是结果中的@eerror中的值还是并不是0，变成3902了，但是返回值错误是不可避免的了


图十一：

在rollback之后，加上return之后，就会完全完全正常了，但是我不确定这里的return，跟面向对象语言中的return是不是完全一样的

加上return的存储过程，就不在一一描述了，直接看图了



所以这里疑问就是return的用法问题，能不能这样return，还有就是存储过程中多个sql，卸载一块放在一个事物中还是分别放在一个事物中，异常了用return返回呢?

[--小F--]

事务的独立性决定了各个事务之间独立运行 原子性决定了只要不提交就回滚

[lvfeng19806001]

我在存储过程调用存储过程时，用return 数值来返回自定义错误信息。。

[AcHerat]

如果按楼主的做法来，建议定义变量，每次将@@error赋给变量，用变量判断。

另外建议用

try

catch

来获取成功与否！

[chuanzhang5687]

提示的很明显啊，主键不能插入重复值。

[快溜]

好长啊。

[AcHerat]

类似这样！

create table employee (empid int, fname varchar(50),lname varchar(50))
insert into employee  (empid, fname, lname) values (2021115, 'jim', 'kennedy') 
go
create procedure [dbo].[updatewanjun]
@upret varchar(10),
@returnval int output
as   
begin     
  
   begin transaction t  
   begin try      
       update employee set empid=@upret where fname ='jim';               
       set @returnval=-1;
       commit 
   end try
   begin catch          
       set @returnval=1;
       rollback 
   end catch
 end
go
 
declare @returnval int ;
exec updatewanjun '123',@returnval output
select @returnval

/*
-1
*/

declare @returnva int ;
exec updatewanjun 'aa',@returnva output
select @returnva

/*
1
*/

drop proc [updatewanjun]
drop table employee

[WYhack]

[Quote=引用 4 楼 acherat 的回复:]

类似这样！

SQL code

create table employee (empid int, fname varchar(50),lname varchar(50))
insert into employee (empid, fname, lname) values (2021115, 'jim', 'kennedy')
go
create procedure [dbo].[u……
[/Quote]

那多个sql，任何一个异常之后用不用return呢？

[快溜]

[Quote=引用 5 楼 wyhack 的回复:]
那多个sql，任何一个异常之后用不用return呢？
[/Quote]
用try catch异常处理保护代码，如果有异常全部回滚，上个帖子里我有写过例子，你看不上吗。

[AcHerat]

上个帖子不是有例子么？ 没试？

create table tb(id int identity(1,1),ic int unique)
insert into tb select 2
go

create proc pro
    @i int,
    @j int output,
    @k int output
AS
BEGIN
    begin try
      begin tran
        insert into tb select @i
        set @j = 0
        commit
    end try    
     begin catch
         rollback
         set @j=1
     end catch

    begin try
      begin tran
        update tb set ic = 1 where id = @i
        set @k = 0
        commit
    end try    
     begin catch
         rollback
         set @k=1
     end catch
END
GO

declare @m int,@n int
exec pro 1,@m output,@n output
select * from tb
select @m,@n

drop proc pro
drop table tb

/***********

id          ic
----------- -----------
1           2
2           1

(2 行受影响)

            
----------- -----------
0           1

(1 行受影响)

[WYhack]

(涩(*^__^*)郎)


不是看不上，还没有试，不知道catch里面会不会和return一样终止sql的执行？等下我试试。

另外就是我想知道return在sql中的用法问题，谢谢

[勿勿]

try cath 下