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

CREATE PROCEDURE [dbo].[sp_DropMedicalRequest] 
	@MedicalRequestID uniqueidentifier
AS
BEGIN

update s
set MedicalRequestRemek = null
from [IES].T_SCHET s
where MedicalRequestRemek = @MedicalRequestID

delete mrs
from [IES].T_MEDICAL_REQUEST_SCHET mrs
where mrs.MedicalRequest = @MedicalRequestID

delete mrsi
from [IES].T_MEDICAL_REQEST_SUBITEM mrsi
join [IES].T_MEDICAL_REQUEST_ITEM mri on mri.MedicalRequestItemID = mrsi.MedicalRequestItem
where mri.MedicalRequest = @MedicalRequestID

delete md
from [IES].T_MEDICAL_DESCRIPTION md
join [IES].T_MEDICAL_REQUEST_ITEM mri on mri.MedicalRequestItemID = md.MedicalRequestItem
where mri.MedicalRequest = @MedicalRequestID

delete me
from [IES].T_MEDICAL_ERROR me
join [IES].T_MEDICAL_REQUEST_ITEM mri on mri.MedicalRequestItemID = me.MedicalRequestItem
where mri.MedicalRequest = @MedicalRequestID

delete mri
from [IES].T_MEDICAL_REQUEST_ITEM mri
where mri.MedicalRequest = @MedicalRequestID

delete mre
from [IES].T_MEDICAL_REQUEST_EXPERT mre
where mre.MedicalRequest = @MedicalRequestID

END
