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

CREATE PROCEDURE ehr.[sp_EhrDeleteRefBooks]
    @IdRefB int
AS
BEGIN
	declare @EhrTableName varchar(200)

	select @EhrTableName = 'T_EHR_RUS_' + rb.Code
	from EHR.T_EHR_RUS_REF_BOOKS rb
	where rb.EhrRusRefBooksID  = @IdRefB

	if(exists(select * from EHR.T_EHR_REFERENCE where EhrRusRefBooks = @IdRefB))
	begin
		raiserror ('Есть записи в таблице зависимостей справочников!', 16, 10)
	end

	delete el 
	from EHR.T_ELEMENT_EHR_REPLACEMENT el
	join  EHR.T_EHR_REPLACEMENT e on e.EhrReplacementID = el.EhrReplacement
	where e.OldEhrRefBook = @IdRefB or e.NewEhrRefBook = @IdRefB

	delete from EHR.T_EHR_REPLACEMENT where OldEhrRefBook = @IdRefB or NewEhrRefBook = @IdRefB
	
	select st.name as ehrtable,sch.name as sch,stparent.name as tbl,cc.name as colname, cast(ROW_NUMBER() over(order by stparent.name)as varchar(50)) rowNum 
	into #TableCol
	from sys.tables st 
	join sys.foreign_keys f on f.referenced_object_id = st.object_id
	join sys.tables stparent on stparent.object_id = f.parent_object_id
	join sys.schemas sch on sch.schema_id = stparent.schema_id
	join sys.foreign_key_columns fkc on fkc.constraint_object_id = f.object_id
	join sys.columns cc on cc.column_id = fkc.parent_column_id and cc.object_id = fkc.parent_object_id
	where st.name = @EhrTableName and f.parent_object_id <> f.referenced_object_id

	IF object_id('tempdb..#EhrDelTable') IS NOT NULL DROP TABLE #EhrDelTable
	create table #EhrDelTable(EhrRusId int)

	declare @Execute nvarchar(max)

	select @Execute =  (SELECT STUFF((
								    SELECT case when tt.rowNum = '1' then 'insert into #EhrDelTable(EhrRusId) ' else 'union ' end + 'select distinct(d' + tt.rowNum + '.' + tt.colname + ') from ' + tt.sch + '.' + tt.tbl + ' d' + tt.rowNum 
															+ ' join EHR.' + tt.ehrtable + ' ehr on d' + tt.rowNum + '.' + tt.colname + ' = ehr.EhrRusID and ehr.EhrRusRefBooks = ' + Cast(@IdRefB as varchar(50))
									from #TableCol tt
								    FOR XML PATH('')), 1, 0, ''))
	--select @Execute 
	EXEC Sp_EXECUTESQL @Execute;

	drop table #TableCol

	if(exists(select Count(*) from #EhrDelTable))
	begin
		select @Execute = 'delete ehr from EHR.' + @EhrTableName + ' ehr where ehr.EhrRusRefBooks = ' + Cast(@IdRefB as varchar(50)) + ' and not exists(select 1 from #EhrDelTable e where e.EhrRusId = ehr.EhrRusID) 

							if(exists(select 1 from EHR.' + @EhrTableName +' ehr where ehr.EhrRusRefBooks = ' + Cast(@IdRefB as varchar(50)) + '))
							begin
								update EHR.T_EHR_RUS_REF_BOOKS set Deleted = 1 where EhrRusRefBooksID = ' +  Cast(@IdRefB as varchar(50)) + ' 
							end
							else
							begin
								delete from EHR.T_EHR_RUS_REF_BOOKS where EhrRusRefBooksID = ' +  Cast(@IdRefB as varchar(50)) + '
							end'
		EXEC Sp_EXECUTESQL @Execute;
	end
	else
	begin
		select @Execute = 'delete ehr from EHR.' + @EhrTableName + ' ehr where ehr.EhrRusRefBooks = ' + Cast(@IdRefB as varchar(50))

		EXEC Sp_EXECUTESQL @Execute;

		delete from EHR.T_EHR_RUS_REF_BOOKS where EhrRusRefBooksID = @IdRefB
	end
	drop table #EhrDelTable;
END