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 ('Есть записи в таблице зависимостей справочников(EHR_REFERENCE)!', 16, 10)
	end
    else
        begin
	        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 * from sys.all_columns ac
				join sys.tables t on t.object_id = ac.object_id
				 where ac.name = 'EhrRusTree' and t.name = @EhrTableName ))
	begin
	  select @Execute = 'update EHR.' + @EhrTableName + ' set EhrRusTree = null where EhrRusRefBooks = ' + Cast(@IdRefB as varchar(50))
	end

	if(exists(select Count(*) from #EhrDelTable))
	begin

		select @Execute = isnull(@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 = isnull(@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
END