--- Заполнение параметра холдера ExhangeMedicalInfoProcessHolder.FluorographyMedicalServiceCodes кодами через разделитель |

CREATE TABLE #FG_CODES	(
	code varchar(128) not null
)
GO

INSERT INTO #FG_CODES (code)
VALUES 
	('A06.09.007'), 
	('A06.09.008.001'), 
	('A06.09.005.003'),
	('A06.09.007.002'), 
	('A06.09.011'),
	('A06.09.005'),
	('A06.09.006'),
	('A06.09.006.001')
GO

INSERT INTO #FG_CODES (code)
	SELECT DISTINCT 
		ms1.Code
	FROM AKUZ.T_PRG_DIAGNOSTICS pd
	JOIN AKUZ.T_DIAGNOSTICS_RESULTS dr on pd.MedicalRecordID = dr.MedicalRecordID
	JOIN AKUZ.T_RESEARCH r on dr.Research = r.MedicalServiceID
	JOIN AKUZ.T_MEDICAL_SERVICE ms1 on r.MedicalServiceID = ms1.MedicalServiceID
	WHERE NOT EXISTS (
		SELECT code
		FROM #FG_CODES c
		WHERE C.code LIKE MS1.Code)
GO

UPDATE p
SET p.ParameterValue = (
	SELECT c.code + '|'
	FROM #FG_CODES c
	FOR XML PATH ('')
)
FROM VCLib.T_HOLDER_PARAMETER p
WHERE p.ParameterName like 'FluorographyMedicalServiceCodes' 
	and holder = (SELECT TOP 1 H.HolderID 
					FROM VCLib.T_HOLDER h	
					WHERE h.name like 'ExhangeMedicalInfoProcessHolder')
GO

DROP TABLE #FG_CODES
GO

