存储过程中的事物,该如何处理

存储过程中的事物
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Member_Update]
@Id uniqueidentifier,
@ZwId uniqueidentifier,
@HyId uniqueidentifier,
@MzId uniqueidentifier,
@JycdId uniqueidentifier,
@GzdqId uniqueidentifier,
@JzdqId uniqueidentifier,
@Gzdz nvarchar(200),
@Jzdz nvarchar(200),
@Name nvarchar(50),
@Age int,
@IsTjr int,
@Gender int,
@Birthday datetime,
@Mobile nvarchar(50),
@Email nvarchar(50),
@Grsr int,
@Jtsr int,
@Hyzk int,
@IsCar int,
@IsHouse int,
@IsJiaZhao int,
@WeiXinId nvarchar(50),
@LoginPwd nvarchar(50),
@HomeTel nvarchar(50),
@QQ nvarchar(50),
@Deleted int,
@LoginName nvarchar(50),
@Result int output
AS
SET @Result = 0 --发生错误
SET XACT_ABORT ON --设置发生错误的回滚
BEGIN TRANSACTION  --开启事务
DECLARE @ref INT
SELECT @ref= COUNT(1) FROM MEMBER WHERE Name=@Name and Deleted=0 --会员名不能相同
IF(@ref>1)
BEGIN
SET @Result = 2
ROLLBACK TRANSACTION
RETURN
END
DECLARE @count INT
SELECT @count=COUNT(1) FROM MEMBER WHERE LoginName=@LoginName and Deleted=0 --登录名不能相同
IF(@count>1)
BEGIN
SET @Result = 3
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
UPDATE [Member] SET
[ZwId] = @ZwId,[HyId] = @HyId,[MzId] = @MzId,[JycdId] = @JycdId,[GzdqId] = @GzdqId,
[JzdqId] = @JzdqId,[Gzdz] = @Gzdz,[Jzdz] = @Jzdz,[Name] = @Name,[Age] = @Age,
[Gender] = @Gender,[Birthday] = @Birthday,[Mobile] = @Mobile,[Email] = @Email,
[Grsr] = @Grsr,[Jtsr] = @Jtsr,[Hyzk] = @Hyzk,[IsCar] = @IsCar,[IsHouse] = @IsHouse,
[IsJiaZhao] = @IsJiaZhao,[WeiXinId] = @WeiXinId,[LoginPwd] = @LoginPwd,
[IsTjr]=@IsTjr,[HomeTel] = @HomeTel,[QQ] = @QQ,[Deleted] = @Deleted,[LoginName]=@LoginName
WHERE Id=@Id
SET @Result=1
COMMIT TRANSACTION
RETURN
END

我在后台程序里面的到输出的值Result,最后得到Result=0,并且没有rollback,执行完了的,此条sql执行成功,但返回却是0,失败,这是为什么,求大神帮忙

这是dal层返回
int num =  SQLHelper.RunProcedure("Member_Update", parameters, out rowsAffected);
            return num;
num为0

这是controller里面最后 return Content(ReturnJson("e", "", false));
  int ret=mb.UpdateProc(model);
            if (ret == 1)
            {
                //成功
                return Content(ReturnJson("y", "", false));
            }
            else if ( ret== 2)
            {
                //会员名不能相同
                return Content(ReturnJson("c","",false));
            }
            else if (ret == 3)
            {
                //登录名不能相同
                return Content(ReturnJson("a","",false));
            }
            return Content(ReturnJson("e", "", false));
        }
------解决方案--------------------
CREATE PROCEDURE [dbo].[nb_order_insert](
@o_buyerid int ,
@o_id bigint OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
IF(EXISTS(SELECT * FROM [Shop] WHERE [s_id] = @o_shopid))
BEGIN
INSERT INTO [Order](o_buyerid )
VALUES (@o_buyerid )
SET @o_id = @@IDENTITY
RETURN 1 — 插入成功返回1
END
ELSE
RETURN 0 — 插入失败返回0
END
------解决方案--------------------
虽然我不是太明白，但感觉貌似return 的值和output的值没有弄明白

存储过程中的事物,该如何处理

相关推荐