购物车
alter proc Buy(@ProductNo char(14),@SkuId int,@account nvarchar(20),@buyNumber int)
as
begin
–使用事务
begin tran
declare @error int
declare @orderNo char(14)
declare @amount float
select @orderNo=OrderNo from [order] where OrderAccount=@account
–查询编号
if(@orderNo is null or @orderNo=’’)
begin
set @orderNo=CONVERT(varchar(100), GETDATE(), 112)+‘0001’
end
—先插入到order_detail表
insert order_detail
select @orderNo,@SkuId,@ProductNo,DetailPrice,@buyNumber,a.ProductName from Product a inner join ProductDetail b on a.ProductNo=b.ProdcutNo
set @error+=@@ERROR
if(@error>0)
begin
rollback
end
–往订单表(购物车)
select @amount=(Number*Price) from [order] a inner join order_detail b on a.orderNO=B.ORDERNO where A.orderaccount=@account
insert [order]
select @orderNo,@account,getdate(),‘待付款’,@amount from ProductDetail
set @error+=@@ERROR
if(@error>0)
begin
rollback
end
—库存扣减
declare @sku int
select @sku=DetailSku from ProductDetail where ProdcutNo=@ProductNo
if(@sku-@buyNumber<0)
begin
rollback
end
else
begin
update ProductDetail set DetailSku=DetailSku-@buyNumber where ProdcutNo=@ProductNo
end
commit
end
select *from Product
exec Buy '202005260001 ',1,‘zhangshan’,10