sql server 存储过程

USE [SupplierERP]
GO
/****** Object: StoredProcedure [dbo].[UpdateInStorage] Script Date: 2021/8/25 15:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: xyx
– Create date: 2021
– Description: balabala
– =============================================
CREATE PROCEDURE [dbo].[UpdateInStorage]
@orderID int,
@inStorageId int
AS
declare @buyUserID int , --买方用户ID
@saleUserID int, --卖方用户ID
@Num int, --数量
@price decimal(18, 4), --价格
@partNO nvarchar(100), – 型号
@objname nvarchar(100), – 品牌
@objCname nvarchar(100),
@objAname nvarchar(100),
@pack nvarchar(100), --封装
@batchNo nvarchar(100), --批号
@unit nvarchar(50), --单位
@minPage int, --最小包装
@insID int, --库存ID
@mfcID int, --品牌ID
@partNoID int, --型号ID
@providerID int, --供应商ID
@companyName nvarchar(100), --供应商名称
@PrimaryLinkMan nvarchar(100), --联系人名称
@Phone nvarchar(100), --电话
@myError int
;
BEGIN
set @myError=0;
set @insID = 0;
set @mfcID=0;
set @partNoID=0;
set @providerID=0;

SET NOCOUNT ON;

select @Num=Num,@price=Price,@buyUserID=买方ID,@saleUserID=卖方ID from [dbo].[ERP_Order] where ID=@orderID;
select @companyName=Company,@PrimaryLinkMan=NickName,@Phone=Phone from [dbo].[ERP_User] where objid=@saleUserID;

select @partNO=part.PartNO,@objname=mfc.objname,@objAname=mfc.objAname,@objCname=mfc.objCname,@pack=ins.封装,@batchNo=ins.批号,@unit=part.Unit,@minPage=part.MinPage
from [dbo].[ERP_InStorage] ins
join [dbo].[ERP_PartNo] part on part.objid=ins.型号
join [dbo].[ERP_MFC] mfc on mfc.objid=ins.品牌
where ins.ID = @inStorageId and ins.CreateUser = @saleUserID ;

begin transaction 
--根据 型号，品牌，封装，批号 寻找卖方对应的库存信息，如果存在，跟新数量，不存在则新增
SELECT @insID=ins.ID from [SupplierERP].[dbo].[ERP_InStorage] ins join [dbo].[ERP_PartNo] part on part.objid=ins.型号
join [dbo].[ERP_MFC] mfc on mfc.objid=ins.品牌 where part.PartNO=@partNO and (mfc.objname=@objname or mfc.objAname=@objAname or mfc.objCname=@objCname ) and ins.封装=@pack and ins.批号=@batchNo and ins.CreateUser=@buyUserID

--修改订单状态
update [SupplierERP].[dbo].[ERP_Order] set OStatus=3 where ID=@orderID;
set @myError=@myError+@@ERROR; 
--修改运单状态
update [SupplierERP].[dbo].[ERP_User_YunDan] set State=1 where objid=@orderID;
set @myError=@myError+@@ERROR; 

if @insID=0
begin
	 --品牌
	 select @mfcID=objid from [dbo].[ERP_MFC] where objuid=@buyUserID and (objname=@objname or objAname=@objAname or objCname=@objCname);
	 if @mfcID=0
	 begin
		insert into [dbo].[ERP_MFC]([objname],[objAname],[objCname],[objuid],[objkilled],[objctime],[objStatus]) 
		values (@objname,@objAname,@objCname,@buyUserID,0,GETDATE(),1) select @mfcID=@@IDENTITY;
		set @myError=@myError+@@ERROR; 
	end 
	--型号
	select @partNoID=objid from [dbo].[ERP_PartNo] where [objuid]=@buyUserID and [PartNO]=@partNO and [MFC]=@mfcID and [Pack]=@pack and [BathcNo]=@batchNo;
	if @partNoID=0
	begin
		insert into [dbo].[ERP_PartNo]([PartNO],[MFC],[Pack],[BathcNo],[Unit],[MinPage],[objuid],[objctime],[Killed],[Status]) 
		values(@partNO,@mfcID,@pack,@batchNo,@unit,@minPage,@buyUserID,GETDATE(),0,0) select @partNoID=@@IDENTITY;
		set @myError=@myError+@@ERROR;    
	end
	--供应商
	select @providerID=objid from [dbo].[ERP_Provider] where [objcid]=@buyUserID and ([Name]=@companyName or FullName=@companyName );
	if @providerID=0
	begin
		insert into [dbo].[ERP_Provider]([Name],[FullName],[PrimaryLinkMan],[Phone] ,[Killed],[objcid],[objctime])
		values(@companyName,@companyName,@PrimaryLinkMan,@Phone,0,@buyUserID,GETDATE()) select @providerID=@@IDENTITY;
		set @myError=@myError+@@ERROR;    
	end

	insert into [dbo].[ERP_InStorage] ([型号],[品牌],[数量],[进价],[售价],[批号],[封装],[最小起订量],[最小包装],[供应商],[CreateUser],[CreateTime],[IsKill],[现价])
	values(@partNoID,@mfcID,@Num,@price,@price,@batchNo,@pack,@minPage,@minPage,@providerID,@buyUserID,GETDATE(),0,@price);
	set @myError=@myError+@@ERROR;    
end
 else
begin
	update [ERP_InStorage] set 数量=数量+@Num where ID=@insID;
	set @myError=@myError+@@ERROR;    
end
if @myError>0
begin 
    rollback transaction
end
else
begin
    commit transaction
end
set nocount off;

END