订单事务-存储过程



create proc Createorder @orderId nvarchar(50),--订单号 @userId int,--用户编号 @address nvarchar(255),--收货人地址 @totalMoney money output --总金额 as begin declare @error int set @error=0 begin transaction --计算总价 select @totalMoney=SUM

create proc Createorder
@orderId nvarchar(50),--订单号
@userId int,--用户编号
@address nvarchar(255),--收货人地址
@totalMoney money output --总金额
as
begin
declare @error int
set @error=0
begin transaction
--计算总价
select @totalMoney=SUM([count]*Unitprice)from Cart
inner join Books on Cart.BookId=Books.Id
where UserId=@userId
set @error=@@ERROR+@error
--向订单主表中插入数据
insert into Orders(OrderId,OrderDate,UserId,TotalPrice,PostAddress,[state])
values(@orderId,GETDATE(),@userId,@totalMoney,@address,0)
set @error=@@ERROR+@error
--向订单明细表中插入数据
insert into OrderBook(OrderID,BookID,Quantity,UnitPrice)
select @orderId,BookId,[Count],UnitPrice from Cart inner join Books on Cart.BookId=Books.Id
where Cart.UserId=@userId
set @error=@@ERROR+@error
--删除购物车表中的数据
delete from Cart where UserId=@userId
set @error=@@ERROR+@error
--判断错误，执行事务
if @error>0
begin
rollback transaction
end
else
begin
commit transaction
end
end
--rollback transaction
--commit transaction
--primary key(Id) identity(1,1)