create PROC [dbo].[NBC_SynchroMaterial]
@userID nvarchar(50),
@exceptionMessage nvarchar(500) out 
as
begin
declare @nResult int; --同步多少条物料
  
--物料类别 1：精华液；2：半成品（罐装半成品）；3：其他
--原料类别 1：普通粉料，2：易挥发粉料；3：大料 4：小料5：香精

declare @totalRows    int;  
set @nResult=0;
set @exceptionMessage='';
--建立临时数据表  
create table #tmpMateriel 
(
[id] int IDENTITY(1,1) ,
MaterielGUID nvarchar(50),
MaterielCode nvarchar(50),
MaterielName  nvarchar(255),
ParentNode nvarchar(50),
IfLeafNode bit,
Material_Category int null,
Raw_Material_Category int null
)  
 
declare @MaterielGUID nvarchar(50);
declare @MaterielCode nvarchar(50);
declare @MaterielName nvarchar(50);
declare @ParentNode nvarchar(50);
declare @IfLeafNode bit;
declare @Material_Category int;
declare @Raw_Material_Category int;

Begin Try
 BEGIN TRAN
 
 DECLARE @tran_error int;
 SET @tran_error = 0;
 
 --读取物料中间表数据，插入到临时表
insert into #tmpMateriel(MaterielGUID,MaterielCode,MaterielName,ParentNode,IfLeafNode,Material_Category,Raw_Material_Category)
SELECT [MaterielGUID]
      ,[MaterielCode]
      ,[MaterielName]
      ,[ParentNode]
      ,[IfLeafNode]
      ,CASE WHEN (MaterielCode like '1.03%' or MaterielCode like '2.02%')  THEN 1
           WHEN (MaterielCode like '1.04%' or MaterielCode like '2.03%') THEN 2
           ELSE 3
       END
      ,CASE WHEN [IfLeafNode]=1 THEN 4  --小料
       ELSE null
       END
            
  FROM [ChwareNBCInterface].[dbo].[Tab_Materiel_Interface] where [If_Read]=0 order by [WriteDT];
  

  --循环遍历临时表
 WHILE EXISTS(SELECT MaterielGUID FROM #tmpMateriel)
begin
    SELECT top 1 @MaterielGUID=MaterielGUID
    ,@MaterielCode=MaterielCode
    ,@MaterielName=MaterielName
    ,@ParentNode=ParentNode
    ,@IfLeafNode=IfLeafNode
    ,@Material_Category=Material_Category
    ,@Raw_Material_Category=Raw_Material_Category
    FROM #tmpMateriel
    if EXISTS(SELECT [MaterialCode] FROM [ChwareNBC].[dbo].[Tab_Material] where MaterialCode=@MaterielCode)
      begin
      --更新mes物料/产品
        UPDATE [ChwareNBC].[dbo].[Tab_Material]
           SET [MaterialName] = @MaterielName
              ,[ParentNode] =@ParentNode
              ,[IfLeafNode] = @IfLeafNode
              ,[MaterialCategory] = @Material_Category
              ,[RawMaterialCategory] = @Raw_Material_Category
              ,[ModifyUser] = @userID
              ,[ModifyDT] = GETDATE()
           WHERE [MaterialCode]=@MaterielCode;
     --更新接口表读取状态  
      update [ChwareNBCInterface].[dbo].[Tab_Materiel_Interface] set [If_Read]=1,ReadDT=GETDATE()where [MaterielGUID]=@MaterielGUID;
      --从临时表中移除当前遍历的物料
      DELETE FROM #tmpMateriel WHERE MaterielGUID=@MaterielGUID;
      set @nResult=@nResult+1;
      end
    else
       begin
        --插入一条物料/产品数据到mes
       INSERT INTO [ChwareNBC].[dbo].[Tab_Material]
           ([MaterialCode]
           ,[MaterialName]
           ,[ParentNode]
           ,[IfLeafNode]
           ,[MaterialCategory]
           ,[RawMaterialCategory]
           ,[CreateUser]
           ,[CreateDt])
     VALUES
           (@MaterielCode
           ,@MaterielName
           ,@ParentNode
           ,@IfLeafNode
           ,@Material_Category
           ,@Raw_Material_Category
           ,@userID
           , GETDATE());
   
     --更新接口表读取状态  
      update [ChwareNBCInterface].[dbo].[Tab_Materiel_Interface] set [If_Read]=1,ReadDT=GETDATE()where [MaterielGUID]=@MaterielGUID;
      --从临时表中移除当前遍历的物料
     DELETE FROM #tmpMateriel WHERE MaterielGUID=@MaterielGUID;
     set @nResult=@nResult+1;
      end
end
End Try
Begin Catch
  --同步出现错误
  SET @tran_error = @tran_error + 1;
  set @ExceptionMessage=error_message();
End Catch

IF(@tran_error > 0)
    BEGIN
        --执行出错，回滚事务
        ROLLBACK TRAN;
        --恢复接口表读取状态为零  
             set @nResult=0;
             select @nResult; --同步成功多少条数据
    END
ELSE
    BEGIN
        --没有异常，提交事务
        COMMIT TRAN;
        --删除临时表
        drop table #tmpMateriel;
        select @nResult; --同步成功多少条数据
    END
end