--测试数据 create table a (cno char(4),name varchar(10), price decimal(8,2)) insert into a values (1001,'张三',100.00), (1002,'李四',18.00), (1003,'王五',10.00), (1004,'赵六',100.00)create table b (sno int identity(1,1),cno1 char(4),cno2 char(4),price decimal(8,2),statu int ,gendate datetime) --表B 里面钱从CNO1转到CNO2. --存储过程代码 create proc test @cno1 char(4),@cno2 char(4),@price decimal(8,2)
as begin tran declare @price_cno1 decimal(8,2),@nowdate datetime=getdate() select @price_cno1=price from A where cno=@cno1 if @price_cno1<@price begin insert into b values (@cno1,@cno2,@price,1,@nowdate) print '余额不足,请重新确认!' return end else begin insert into b values (@cno1,@cno2,@price,0,@nowdate) end commit --2次调用 exec test '1001','1002',110.00 exec test '1001','1002',80.00 --实际用插入成功了 还需要修改A表信息。
--测试数据
create table a (cno char(4),name varchar(10), price decimal(8,2))
insert into a values
(1001,'张三',100.00),
(1002,'李四',18.00),
(1003,'王五',10.00),
(1004,'赵六',100.00)create table b (sno int identity(1,1),cno1 char(4),cno2 char(4),price decimal(8,2),statu int ,gendate datetime)
--表B 里面钱从CNO1转到CNO2.
--存储过程代码
create proc test
@cno1 char(4),@cno2 char(4),@price decimal(8,2)
as
begin tran
declare @price_cno1 decimal(8,2),@nowdate datetime=getdate()
select @price_cno1=price from A
where cno=@cno1
if @price_cno1<@price
begin
insert into b values (@cno1,@cno2,@price,1,@nowdate)
print '余额不足,请重新确认!'
return
end
else
begin
insert into b values (@cno1,@cno2,@price,0,@nowdate)
end
commit
--2次调用
exec test '1001','1002',110.00
exec test '1001','1002',80.00
--实际用插入成功了 还需要修改A表信息。