关于使用游标对数据库中的历史数据的进行修改

USE [NewFp_ZhenZhou]
GO

/****** Object:  StoredProcedure [dbo].[code_contrastBycm3andicd]    Script Date: 2018/05/30 10:51:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[code_contrastBycm3andicd]
AS
BEGIN
declare @errormsg nvarchar(max)
declare @id int
declare @name nvarchar(500)
declare @code nvarchar(50)
declare @lccode nvarchar(50)
declare @lcname nvarchar(500)
declare @SourceSql1 nvarchar(50)--分割‘+’之前的字符串
declare @SourceSql2 nvarchar(50)--分割‘+’之后的字符串
BEGIN TRY
    Begin TRAN 
----处理手术编码名称不一致
     print '开始处理手术编码名称不一致' 
       DECLARE contact_cursor  CURSOR FOR
            --select vb.bingah,vb.ZHUYCSH,vb.HIS_KEYNO from  tmp_brzyxx vb 
                 select * from cm3dz_code
          open contact_cursor 
         FETCH NEXT FROM contact_cursor into @id,@code,@name,@lccode,@lcname

         print '开始处理:' 
         while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
           begin
             set @errormsg='update bingrenshoushuxinxi set d_cm3_code='''+@lccode+''',d_cm3_name='''+@lcname+''' where d_cm3_code='''+@code+''' and d_cm3_name='+@name+''''
             update bingrenshoushuxinxi set d_cm3_code=@lccode,d_cm3_name=@lcname where d_cm3_code=@code and d_cm3_name=@name
             fetch next from contact_cursor   into @id,@code,@name,@lccode,@lcname --移动游标
           end 

         CLOSE contact_cursor  
         DEALLOCATE contact_cursor  

----处理手术编码一致名称不一致
     print '开始处理手术编码一致名称不一致' 
       DECLARE contact_cursor_cm3code  CURSOR FOR
            --排出在 手术编码名称不一致 处理过的数据
                 select * from cm3dz_name where id not in(select id from cm3dz_name where lccode+lcname in(select lccode+lcname from cm3dz_code))
          open contact_cursor_cm3code
         FETCH NEXT FROM contact_cursor_cm3code into @id,@code,@name,@lccode,@lcname

         print '开始处理:'
         while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
           begin
             set @errormsg='update bingrenshoushuxinxi set d_cm3_code='''+@lccode+''',d_cm3_name='''+@lcname+''' where d_cm3_code='''+@code+''' and d_cm3_name='''+@name+''''
             update bingrenshoushuxinxi set d_cm3_code=@lccode,d_cm3_name=@lcname where d_cm3_code=@code and d_cm3_name=@name
             fetch  next from contact_cursor_cm3code   into @id,@code,@name,@lccode,@lcname --移动游标
           end 

         CLOSE contact_cursor_cm3code  
         DEALLOCATE contact_cursor_cm3code
  
--处理手术编码不一致名称一致
     print '开始处理手术编码不一致名称一致' 
       DECLARE contact_cursor_1  CURSOR FOR
            --
                 select * from cm3dz
          open contact_cursor_1
         FETCH NEXT FROM contact_cursor_1 into @id,@code,@name

         print '开始处理:'
         while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
           begin
             select @lccode=code from d_cm3 where name=@name
             set @errormsg='update bingrenshoushuxinxi set d_cm3_code='''+@lccode+''' where d_cm3_name='''+@name+''''
             update bingrenshoushuxinxi set d_cm3_code=@lccode where d_cm3_name=@name
             fetch  next from contact_cursor_1   into @id,@code,@name --移动游标
           end 
         CLOSE contact_cursor_1  
         DEALLOCATE contact_cursor_1  

----处理疾病编码一致名称不一致
      print '开始处理疾病编码一致名称不一致' 
       DECLARE contact_cursor_icdcode  CURSOR FOR
            --select vb.bingah,vb.ZHUYCSH,vb.HIS_KEYNO from  tmp_brzyxx vb 
                 select * from icddz_code
          open contact_cursor_icdcode 
         FETCH NEXT FROM contact_cursor_icdcode into @id,@code,@name,@lccode,@lcname

         print '开始处理:'
         while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
          begin
              set @errormsg='update BingRenMenZhenZhenDuan set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
         
             if(CHARINDEX('+',@lccode)>0)
               begin
                  SET @SourceSql1 = substring(@lccode,1,charindex('+',@lccode))
                  SET @SourceSql2 = substring(@lccode,charindex('+',@lccode)+1,LEN(@lccode))
                  set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@SourceSql1+''',d_icd_icd_s='''+@SourceSql2+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@SourceSql1,d_icd_icd_s=@SourceSql2,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end 
             else
               begin
                 set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end
            update BingRenMenZhenZhenDuan set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
            fetch  next from contact_cursor_icdcode   into @id,@code,@name,@lccode,@lcname --移动游标


            coloe next from contact_cursor_icdcode into 
           end 
         CLOSE contact_cursor_icdcode  
         DEALLOCATE contact_cursor_icdcode 

----处理疾病编码名称不一致
      print '开始处理疾病编码名称不一致' 
       DECLARE contact_cursor_icdname  CURSOR FOR
            --排出在 手术编码名称不一致 处理过的数据
                 select * from icddz_name where id not in(select id from icddz_name where lccode+lcname in(select lccode+lcname from cm3dz_code))
          open contact_cursor_icdname 
         FETCH NEXT FROM contact_cursor_icdname into @id,@code,@name,@lccode,@lcname

         print '开始处理:'
         while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
           begin
             set @errormsg='update BingRenMenZhenZhenDuan set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
             if(CHARINDEX('+',@lccode)>0)
               begin
                  SET @SourceSql1 = substring(@lccode,1,charindex('+',@lccode))
                  SET @SourceSql2 = substring(@lccode,charindex('+',@lccode)+1,LEN(@lccode))
                  set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@SourceSql1+''',d_icd_icd_s='''+@SourceSql2+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@SourceSql1,d_icd_icd_s=@SourceSql2,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end 
             else
               begin
                 set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end
             update BingRenMenZhenZhenDuan set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
             fetch  next from contact_cursor_icdname   into @id,@code,@name,@lccode,@lcname --移动游标
           end 
         CLOSE contact_cursor_icdname  
         DEALLOCATE contact_cursor_icdname 
--处理编码不一致名称一致
      print '开始处理编码不一致名称一致' 
       DECLARE contact_cursor_2  CURSOR FOR
            --
                 select * from icddz
          open contact_cursor_2 
         FETCH NEXT FROM contact_cursor_2 into @id,@code,@name,@lccode,@lcname

         print '开始处理:' 
         while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
           begin
             set @errormsg='update BingRenMenZhenZhenDuan set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
             if(CHARINDEX('+',@lccode)>0)
               begin
                  SET @SourceSql1 = substring(@lccode,1,charindex('+',@lccode))
                  SET @SourceSql2 = substring(@lccode,charindex('+',@lccode)+1,LEN(@lccode))
                  set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@SourceSql1+''',d_icd_icd_s='''+@SourceSql2+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@SourceSql1,d_icd_icd_s=@SourceSql2,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end 
             else
               begin
                 set @errormsg=@errormsg+'update BingRenZhenDuanXinXi set d_icd_icd_10='''+@lccode+''',D_ICD_Name='''+@lcname+''' where d_icd_icd_10='''+@code+''' and D_ICD_Name='''+@name+''''
                 update BingRenZhenDuanXinXi set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
               end
             update BingRenMenZhenZhenDuan set d_icd_icd_10=@lccode,D_ICD_Name=@lcname where d_icd_icd_10=@code and D_ICD_Name=@name
             fetch  next from contact_cursor_2   into @id,@code,@name,@lccode,@lcname --移动游标
           end 
         CLOSE contact_cursor_2  
         DEALLOCATE contact_cursor_2 

        commit TRAN
     PRINT '处理成功';
    END TRY
    BEGIN CATCH    
        rollback TRAN
        PRINT '处理ERROR:'+@errormsg;                           
    END CATCH
END
GO
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值