IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DropMedicalExpertise]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DropMedicalExpertise]
GO

CREATE PROCEDURE [dbo].[sp_DropMedicalExpertise] 
	@Id uniqueidentifier
AS
BEGIN

update smo
set MedicalExpertise = null
from ies.T_SMO_ACT smo
where smo.MedicalExpertise = @Id

delete mee
from [IES].T_MEDICAL_EXPERTISE_ERROR mee
join ies.T_MEDICAL_EXPERTISE_ITEM mei on mei.MedicalExpertiseItemID = mee.MedicalExpertiseItem
join ies.T_MEDICAL_EXPERTISE_SCHET mes on  mei.MedicalExpertiseSchet = mes.MedicalExpertiseSchetID
where mes.MedicalExpertise = @Id and mes.MedicalRequest is null

delete mei
from ies.T_MEDICAL_EXPERTISE_ITEM mei 
join ies.T_MEDICAL_EXPERTISE_SCHET mes on  mei.MedicalExpertiseSchet = mes.MedicalExpertiseSchetID
where mes.MedicalExpertise = @Id

delete meg
from ies.T_MEDICAL_EXPERTISE_GROUP meg
join ies.T_MEDICAL_EXPERTISE_SCHET mes on  meg.MedicalExpertiseReestr = mes.MedicalExpertiseSchetID
where mes.MedicalExpertise = @Id and mes.MedicalRequest is null

delete mes
from ies.T_MEDICAL_EXPERTISE_SCHET mes
where mes.MedicalExpertise = @Id

delete mes
from ies.T_MEDICAL_EXPERTISE_CRITERIA mes
where mes.MedicalExpertise = @Id

delete mes
from ies.T_MEDICAL_EXPERTISE mes
where mes.MedicalExpertiseID = @Id

END
