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