更新地区号存储过程

USE [**]
GO
/****** Object:  StoredProcedure [***].[***]    Script Date: 06/03/2016 09:01:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [***].[llrrGXKH_GJZ]
    @lsbm char(20), 
    @dqbh char(20),
    @serror    varchar(255) output
AS
BEGIN
   declare @vssql varchar(500)
   
   
   
--1.1 根据上游供应商绑定匹配,解决不同客户以同一客户名从不同供应商进货的问题.
           set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khsdzd_sjkhbh,yjlx_dqbh=khsdzd_dqbh,yjlx_xykh= ''1'',yjlx_ppkhmc=khsdzd_sjkhmc '
        set @vssql += ' From khsdzd '
        set @vssql += ' where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khsdzd_khmc)) '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and (khsdzd_js = ''1'' or khsdzd_js = ''2'' or khsdzd_js = ''3'')'
        set @vssql +='  and khsdzd_gysbh = yjlx_gysbh'
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.5 根据上游绑定表(khsdzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end    
--1.2 根据上游供应商绑定匹配
           set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khsdzd_sjkhbh,yjlx_dqbh=khsdzd_dqbh,yjlx_xykh= ''0'',yjlx_ppkhmc=khsdzd_sjkhmc '
        set @vssql += ' From khsdzd '
        set @vssql += ' where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khsdzd_khmc)) '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and (khsdzd_js = ''4'' or khsdzd_js = ''5'' or khsdzd_js = ''6'')'
        set @vssql +='  and khsdzd_gysbh = yjlx_gysbh'
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.5 根据上游绑定表(khsdzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end    
   
--1.3 根据客户名称来匹配,并更新 协议 客户标志为''1'',客户名称==匹配客户名称

        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''1'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khppzd_khmc)) '
        set @vssql += ' and (khppzd_js=''2'' or khppzd_js=''1'' or khppzd_js=''3'') '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1 根据客户名称来匹配,并更新 协议 客户标志为''1'',客户名称==匹配客户名称'
            raiserror(@sError,16,1)
            return -1
            end        
            

--1.5 根据客户别名表(khbmzd)来更新匹配

        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khbmzd_khbh,yjlx_dqbh=khbmzd_dqbh,yjlx_xykh= ''1'',yjlx_ppkhmc=khbmzd_khmc '
        set @vssql += ' From khbmzd '
        set @vssql += ' where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khbmzd_bm)) '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and (khbmzd_js = ''1'' or khbmzd_js = ''2'' or khbmzd_js = ''3'')'
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.5 根据客户别名表(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end    
    
    
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khbmzd_khbh,yjlx_dqbh=khbmzd_dqbh,yjlx_xykh= ''0'',yjlx_ppkhmc=khbmzd_khmc '
        set @vssql += ' From khbmzd '
        set @vssql += ' where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khbmzd_bm)) '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and (khbmzd_js = ''4'' or khbmzd_js = ''5'' or khbmzd_js = ''6'')'
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.5 根据客户别名表(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end    
--1.6 根据客户匹配三个关键字字典（khppzd2）来更新匹配
        --市内更新协议客户
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''1'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd2 '
        set @vssql += ' where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'''        
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz3))+''%'' '
        set @vssql += ' and (khppzd_js=''2'' or khppzd_js=''1'' or khppzd_js=''3'') '
        set @vssql += ' and   (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and left(ltrim(rtrim(yjlx_gysdqbh)),4)=left(ltrim(rtrim(khppzd_dqbh)),4)' 
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.6 根据khppzd2(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end    
            
        --省内更新协议客户
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''1'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd2 '
        set @vssql += ' where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'''        
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz3))+''%'' '
        set @vssql += ' and (khppzd_js=''2'' or khppzd_js=''1'' or khppzd_js=''3'') '
        set @vssql += ' and   (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and left(ltrim(rtrim(yjlx_gysdqbh)),2)=left(ltrim(rtrim(khppzd_dqbh)),2)' 
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.6 根据khppzd2(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end    
        --省外更新协议客户
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''1'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd2 '
        set @vssql += ' where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'''        
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz3))+''%'' '
        set @vssql += ' and (khppzd_js=''2'' or khppzd_js=''1'' or khppzd_js=''3'') '
        set @vssql += ' and   (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.6 根据khppzd2(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end        
                    
        --市内更新非协议客户
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd2 '
        set @vssql += ' where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'''        
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz3))+''%'' '
        set @vssql += ' and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += ' and   (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and left(ltrim(rtrim(yjlx_gysdqbh)),4)=left(ltrim(rtrim(khppzd_dqbh)),4)'
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.6 根据khppzd2(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end    
        --省内更新非协议客户
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd2 '
        set @vssql += ' where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'''        
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz3))+''%'' '
        set @vssql += ' and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += ' and   (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and left(ltrim(rtrim(yjlx_gysdqbh)),2)=left(ltrim(rtrim(khppzd_dqbh)),2)'
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.6 根据khppzd2(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end
        --省外更新非协议客户
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd2 '
        set @vssql += ' where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'''        
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz3))+''%'' '
        set @vssql += ' and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += ' and   (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = '1.6 根据khppzd2(khbmzd)来更新匹配'
            raiserror(@sError,16,1)
            return -1
            end
-- 2 更新非协议客户的客户名称  客户名称==匹配客户名称
        --市内
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khppzd_khmc)) '
        set @vssql += ' and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and left(ltrim(rtrim(yjlx_gysdqbh)),4)=left(ltrim(rtrim(khppzd_dqbh)),4) '
        
        exec(@vssql)
        if @@error <> 0
            begin
            select @serror = ' 2 更新非协议客户的客户名称  客户名称==匹配客户名称,--市内'
            raiserror(@sError,16,1)
            return -1
            end                
        --省内
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khppzd_khmc)) '
        set @vssql += ' and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null )    '
        set @vssql += ' and left(ltrim(rtrim(yjlx_gysdqbh)),2)=left(ltrim(rtrim(khppzd_dqbh)),2) '
        exec(@vssql)
        if @@error <> 0
            begin
            
            select @serror = '2 更新非协议客户的客户名称  客户名称==匹配客户名称,-省内'
            raiserror(@sError,16,1)
            return -1
        end        


        --省外
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' Where ltrim(rtrim(yjlx_khmc))=ltrim(rtrim(khppzd_khmc)) '
        set @vssql += ' and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null )    '
        
        exec(@vssql)
        if @@error <> 0
            begin
            
            select @serror = '2 更新非协议客户的客户名称  客户名称==匹配客户名称,--省外'
            raiserror(@sError,16,1)
            return -1
        end        

--3 根据客户关键字 更新协议客户编号地区编号
    --先在本地区匹配
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''1'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' Where '
        set @vssql += '   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'' '
        set @vssql += ' and  yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and (khppzd_js=''2'' or khppzd_js=''1'' or khppzd_js=''3'' ) '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += ' and left(ltrim(yjlx_gysdqbh),2)=left(ltrim(khppzd_dqbh),2) '

        exec(@vssql)
        if @@error <> 0
            begin
            
            select @serror = '-3 根据客户关键字 更新协议客户编号地区编号,本地区匹配'
            raiserror(@sError,16,1)
            return -1
        end    
--根据地区字典匹配
    --3.1 市内匹配,客户名称包含有地区名称关键字（zwdqzd_bz='0/3'匹配)
   set @vssql= 'UPDATE '+@lsbm + ' set  yjlx_dqbh=zwdqzd_dqbh,yjlx_tzry=zwdqzd_ywy,yjlx_tzrymc=zwdqzd_ywymc '
   set  @vssql += ' From lrdqzd '
   set  @vssql += 'Where yjlx_dqbh='''' '
   set  @vssql += ' and left(yjlx_gysdqbh,4)=left(zwdqzd_dqbh,4) '
   set  @vssql += ' and yjlx_khmc like ''%''+zwdqzd_dqmc+''%'' '
   set  @vssql += ' and (zwdqzd_bz=''0'' or zwdqzd_bz=''3'') '
   exec(@vssql)
   if @@error <> 0
    begin
    rollback
    select @serror = '1 市内匹配,客户名称包含有地区名称关键字'
    raiserror(@sError,16,1)
    return -1
   end

   
   --3.2 市内匹配，客户名称包含（地区名称－1）的关键字（zwdqzd_bz='0/3'匹配)
    set @vssql= ' UPdate '+ @lsbm
    set @vssql += ' set yjlx_dqbh=zwdqzd_dqbh,yjlx_tzry=zwdqzd_ywy,yjlx_tzrymc=zwdqzd_ywymc '
    set @vssql += ' From lrdqzd '
    set @vssql += ' Where yjlx_dqbh=''''  '
    set @vssql += ' and left(yjlx_gysdqbh,4)=left(zwdqzd_dqbh,4)  '
    set @vssql += ' and yjlx_khmc like ''%''+left(zwdqzd_dqmc,len(zwdqzd_dqmc)-1)+''%'''  
    set @vssql += ' and (zwdqzd_bz=''0'' or zwdqzd_bz=''3'') '    
    exec(@vssql)
   if @@error <> 0
    begin
    rollback
    select @serror = '2 市内匹配，客户名称包含（地区名称－1）的关键字（zwdqzd_bz=匹配)'
    raiserror(@sError,16,1)
    return -1
   end    
    
    --3.3 //市内,客户名称包含有地区名称全称（zwdqzd_bz='1'匹配)
            
    set    @vssql = ' UPdate '+ @lsbm
    set    @vssql += ' set yjlx_dqbh=zwdqzd_dqbh,yjlx_tzry=zwdqzd_ywy,yjlx_tzrymc=zwdqzd_ywymc '
    set    @vssql += ' From lrdqzd '
    set    @vssql += ' Where yjlx_dqbh=''''  '
    set    @vssql += ' and left(yjlx_gysdqbh,4)=left(zwdqzd_dqbh,4)  '
    set    @vssql += ' and yjlx_khmc like ''%''+zwdqzd_dqmc +''%'' '  
    set    @vssql += ' and zwdqzd_bz=''1'' '    
    exec(@vssql)
    
   if @@error <> 0
    begin
    rollback
    select @serror = '3 //市内,客户名称包含有地区名称全称（zwdqzd_bz=匹配)'
    raiserror(@sError,16,1)
    return -1
   end    
       --3.4//省内,客户名称包含有地区名称关键字（zwdqzd_bz='0'匹配)
    set @vssql = ' UPdate '+ @lsbm
    set @vssql += ' set yjlx_dqbh=zwdqzd_dqbh,yjlx_tzry=zwdqzd_ywy,yjlx_tzrymc=zwdqzd_ywymc '
    set @vssql += ' From lrdqzd '
    set @vssql += ' Where yjlx_dqbh='''' '
    set @vssql += ' and left(yjlx_gysdqbh,2)=left(zwdqzd_dqbh,2)  '
    set @vssql += ' and yjlx_khmc like ''%''+left(zwdqzd_dqmc,len(zwdqzd_dqmc) - 1) +''%'' '  
    set @vssql += ' and (zwdqzd_bz=''0'' or zwdqzd_bz=''3'') '    
    exec(@vssql)    
   if @@error <> 0
    begin
    rollback
    select @serror = '4//省内,客户名称包含有地区名称关键字（zwdqzd_bz=匹配)'
    raiserror(@sError,16,1)
    return -1
   end    
    
    ----3.5//省内,客户名称包含有地区名称全称（zwdqzd_bz=''1''匹配)
    set @vssql = ' UPdate '+ @lsbm
    set @vssql += ' set yjlx_dqbh=zwdqzd_dqbh,yjlx_tzry=zwdqzd_ywy,yjlx_tzrymc=zwdqzd_ywymc '
    set @vssql += ' From lrdqzd '
    set @vssql += ' Where yjlx_dqbh=''''  '
    set @vssql += ' and left(yjlx_gysdqbh,2)=left(zwdqzd_dqbh,2)  '
    set @vssql += ' and yjlx_khmc like ''%''+zwdqzd_dqmc+''%'' '  
    set @vssql += ' and zwdqzd_bz=''1'' '    
    exec(@vssql)
   if @@error <> 0
    begin
    rollback
    select @serror = '5//省内,客户名称包含有地区名称全称（zwdqzd_bz=''1''匹配)'
    raiserror(@sError,16,1)
    return -1
   end    
    
    --3.6    //省内级数为1 ,客户名称前2位=地区名称前2位
    set @vssql = ' UPdate '+ @lsbm
    set @vssql += ' set yjlx_dqbh=zwdqzd_dqbh,yjlx_tzry=zwdqzd_ywy,yjlx_tzrymc=zwdqzd_ywymc '
    set @vssql += ' From lrdqzd '
    set @vssql += ' Where yjlx_dqbh=''''  '    
    set @vssql += ' and zwdqzd_js=''1'' and left(yjlx_khmc,2)=left(zwdqzd_dqmc,2) '
    set @vssql += ' and zwdqzd_bz =''1'''
    set @vssql += ' and left(zwdqzd_dqbh,2)='''
    set @vssql += @dqbh
    set @vssql += ''''
    exec(@vssql)
   if @@error <> 0
    begin
    rollback
    select @serror = '6    //省内级数为1 ,客户名称前2位=地区名称前2位'
    raiserror(@sError,16,1)
    return -1
   end    
   --3.4 / 乡镇地区字典匹配 XZQHZD  ERP_DQBH------2013-04-08--*/        
    set @vssql = ' UPdate '+ @lsbm
    set @vssql += ' set yjlx_dqbh=ERP_DQBH '
    set @vssql += ' From XZQHZD '
    set @vssql += ' Where yjlx_dqbh=''''  '
    set @vssql += ' and left(yjlx_gysdqbh,4)=left(erp_dqbh,4)  '
    set @vssql += ' and yjlx_khmc like ''%''+left(NAME_XIANG,len(NAME_XIANG) - 1) +''%'' ' 
    exec(@vssql)    
    
   if @@error <> 0
    begin
    rollback
    select @serror = '7 / 乡镇地区字典匹配 XZQHZD  ERP_DQBH------2013-04-08--*'
    raiserror(@sError,16,1)
    return -1
   end    

        
        --后在全国匹配        
        set @vssql = ' Update ' +@lsbm
        set @vssql += ' Set yjlx_khbh=khppzd_khbh,yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''1'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' Where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'' '
        set @vssql += ' and   yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += ' and (khppzd_js=''2'' or khppzd_js=''1'' or khppzd_js=''3'' ) '
        set @vssql += ' and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        
        exec(@vssql)
        if @@error <> 0
            begin
            
            select @serror = '-3 根据客户关键字 更新协议客户编号地区编号,全国匹配'
            raiserror(@sError,16,1)
            return -1
        end        


--4 根据客户关键字 更新非协议客户编号地区编号
    --先在本地区匹配
        set @vssql = ' Update ' +@lsbm
        set @vssql += '  set yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' Where   '
        set @vssql += '    yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'''        
        set @vssql += '  and  yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += '  and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += '  and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        set @vssql += '  and left(ltrim(yjlx_gysdqbh),2)=left(ltrim(khppzd_dqbh),2) '
        exec(@vssql)
        if @@error <> 0
            begin
            
            select @serror = '4 根据客户关键字 更新非协议客户编号地区编号,本地区匹配'
            raiserror(@sError,16,1)
            return -1
        end

        --后在全国匹配
        set @vssql = ' Update ' +@lsbm
        set @vssql += '  set yjlx_dqbh=khppzd_dqbh,yjlx_xykh=''0'',yjlx_ppkhmc=khppzd_khmc '
        set @vssql += ' From khppzd '
        set @vssql += ' Where yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz1))+''%'' '
        set @vssql += '  and  yjlx_khmc like ''%''+ltrim(rtrim(khppzd_gjz2))+''%'' '
        set @vssql += '  and (khppzd_js=''4'' or khppzd_js=''5'' or khppzd_js=''6'') '
        set @vssql += '  and (ltrim(rtrim(yjlx_khbh))='''' or yjlx_khbh is null ) '
        
        exec(@vssql)
    
        if @@error <> 0
            begin
            
            select @serror = '4 根据客户关键字 更新非协议客户编号地区编号,在全国匹配'
            raiserror(@sError,16,1)
            return -1
        end

   
    
END