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(UnitPrice*[Count]) from Books inner join Cart on Books.Id=Cart.BookId where Cart.UserId=@userId
set @error=@@error+@error
--向订单主表中插入数据.
insert into dbo.Orders(OrderId, OrderDate, UserId, TotalPrice, PostAddress, state) values(@orderId,getdate(),@userId,@totalMoney,@address,0)
set @error=@@error+@error
--向明细表中插入数据
insert into dbo.OrderBook(OrderID, BookID, Quantity, UnitPrice)select @orderId,BookId,[Count],UnitPrice from Books inner join Cart on Books.Id=Cart.BookId 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
存储过程示例
最新推荐文章于 2024-06-17 15:00:11 发布