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 mes
set mes.MedicalRequest = null
from [IES].T_MEDICAL_EXPERTISE_SCHET mes
where mes.MedicalRequest = @MedicalRequestID

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

select mri.SchetSluchAccomplished
into #ssa
from [IES].T_MEDICAL_REQUEST_ITEM mri
where mri.MedicalRequest = @MedicalRequestID

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

declare cur cursor
for
  select SchetSluchAccomplished
FROM #ssa

open cur
declare @SsaID uniqueidentifier
fetch next from cur INTO @SsaID
while @@FETCH_STATUS = 0
begin
exec [dbo].[sp_SetExpertiseStatus] @SchetSluchAccomplished = @SsaID
fetch next from cur INTO  @SsaID
end
close cur
deallocate cur

drop table #ssa

END
