新旧数据ICD的整合

--2. 创建Upate_Ref_Education_ICD_ByEduName存储过程，根据EduName和新的ICD串增量更新表
if Exists(select name from sysobjects where NAME = 'Upate_Ref_Education_ICD_ByEduName' and type='P')
    drop procedure Upate_Ref_Education_ICD_ByEduName
GO

CREATE PROC Upate_Ref_Education_ICD_ByEduName @EduName VARCHAR(1000),@newICD VARCHAR(MAX)
AS
--功能: @newICD+@oldICD==>@mergeICD (并更新Ref_Education中对应的ICD)， 但@oldICD中已有的数据不得再添加  
DECLARE @oldICD VARCHAR(MAX),@mergeICD VARCHAR(MAX)

--旧数据， 新数据 (去除空格与换行等)
SELECT @oldICD=CONVERT(VARCHAR(max),ICD) FROM Ref_Education WHERE EduName =@EduName
SET @oldICD =REPLACE(REPLACE(REPLACE(REPLACE(@oldICD, CHAR(13), ''), CHAR(10), ''),CHAR(9),''),' ','')  
SET @newICD =REPLACE(REPLACE(REPLACE(REPLACE(@newICD, CHAR(13), ''), CHAR(10), ''),CHAR(9),''),' ','')

--将表中的数据形成一串  
Select @mergeICD=
	stuff((select ','+id from (  
		SELECT id FROM fn_Split(@oldICD,',') UNION ALL  
		SELECT id FROM fn_Split(@newICD,',') WHERE id NOT IN( SELECT id FROM fn_Split(@oldICD,','))  
) AS T for xml path('')),1,1,'')
PRINT '----------'+@EduName+'----------'
PRINT 'OLD   ICD:'+@oldICD
PRINT 'NEW   ICD:'+@newICD  
PRINT 'MERGE ICD:'+@mergeICD + CHAR(13)

--更新数据
UPDATE Ref_Education SET ICD = @mergeICD WHERE EduName=@EduName
GO