转账存储过程有关问题，求解

转账存储过程问题，求解！
create procedure transpro
@aid1 float, @aid2 float,@num int
as
begindeclare @ye int
declare @error int
begin transaction
select @ye=jine from account where aid=@aid1 if(@num > @ye)
begin
print '卡上余额不足'
rollback transaction
end
else
update account set jine=jine+@num where aid=@aid2
set @error=@error+@@error
update account set jine=jine-@num where aid=@aid1
set @error=@error+@@error
if @error>0
begin
print '转账失败！'
rollback transaction
end
else
begin
print '转账成功！'
commit transaction
end
end 当转账金额为2000 而余额只有800的时候，也能输出转账成功这是怎么回事，不知道哪里有问题，请各位大侠帮忙啊！

------解决方案--------------------
先帮你格式化一下代码，这样没人看的

SQL code


CREATE PROCEDURE transpro
  @aid1 FLOAT,
  @aid2 FLOAT,
  @num  INT
AS
BEGIN
  DECLARE @ye INT
  DECLARE @error INT

  BEGIN TRANSACTION

  SELECT
    @ye = jine
  FROM   account
  WHERE
    aid = @aid1

  IF(@num > @ye)
  BEGIN
    PRINT '卡上余额不足'

    ROLLBACK TRANSACTION
  END
  ELSE
    UPDATE account
    SET    jine = jine + @num
    WHERE  aid = @aid2

  SET @error=@error + @@error

  UPDATE account
  SET    jine = jine - @num
  WHERE  aid = @aid1

  SET @error=@error + @@error

  IF @error > 0
  BEGIN
    PRINT '转账失败！'

    ROLLBACK TRANSACTION
  END
  ELSE
  BEGIN
    PRINT '转账成功！'

    COMMIT TRANSACTION
  END
END

------解决方案--------------------
余额足够才进行转账，看你的代码觉得Else和最后一个End之间的代码是@num <= @ye时才执行的，因此要在Else和最后一个End之间加上Begin...End。

SQL code


CREATE PROCEDURE transpro
  @aid1 FLOAT,
  @aid2 FLOAT,
  @num  INT
AS
BEGIN
  DECLARE @ye INT
  DECLARE @error INT

  BEGIN TRANSACTION

  SELECT
    @ye = jine
  FROM   account
  WHERE
    aid = @aid1

  IF(@num > @ye)
  BEGIN
    PRINT '卡上余额不足'

    ROLLBACK TRANSACTION
  END
  ELSE
  BEGIN
    UPDATE account
    SET    jine = jine + @num
    WHERE  aid = @aid2

    SET @error=@error + @@error

    UPDATE account
    SET    jine = jine - @num
    WHERE  aid = @aid1

    SET @error=@error + @@error

    IF @error > 0
    BEGIN
      PRINT '转账失败！'

      ROLLBACK TRANSACTION
    END
    ELSE
    BEGIN
      PRINT '转账成功！'

      COMMIT TRANSACTION
    END
  END
END

------解决方案--------------------
if /else 如果没有加begin /end 的话，默认只执行最接近这个关键字的那句操作，所以建议楼主要规范化编程。不然会有很多后续问题浪费你时间

转账存储过程有关问题，求解

相关推荐