CREATE procedure [C6MartPeriodicoImmobiliare].[Blacklist_Blocco_Tabellone] @param1 varchar(20)
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
-- [C6MartPeriodico].[Blacklist_Blocco_Tabellone] Blocco
-- [C6MartPeriodico].[Blacklist_Blocco_Tabellone] SBlocco
--select * from c6martperiodico.tabellone where blacklist is not null
--select * from C6MartPeriodico.ExcludedList 
--select * from C6MartPeriodico.ExcludedList_testing
--
--select * from c6martperiodico.tabellone where codice_fiscale='BBBMRA56T09L736R'
--select * from C6MartPeriodico.ExcludedList where codicefiscale='FRILXA72T04H501R'
--select codicefiscale,COUNT(*) from C6MartPeriodico.ExcludedList group by codicefiscale having COUNT(*)>1
--insert into C6MartPeriodico.ExcludedList
--values ('F','BBBMRA56T09L736R','TestF',GETDATE(),1),
--('F','BBBMRA56T09L736R','TestB',GETDATE(),1),
--('F','BBBMRA56T09L736R','TestC',GETDATE(),1),
--('F','BBBMRA56T09L736R','TestF',GETDATE(),1)
----delete from C6MartPeriodico.ExcludedList  where DescrErr='TestC'
if (@param1='BLOCCO')
begin
drop table if exists #riunisco_imm
drop table if exists #rimuovoduplicati_imm
update C6MartPeriodico.tabellone
set blacklist_imm=NULL
select distinct rete,codicefiscale,DescrErr
into #rimuovoduplicati_imm
from  C6MartPeriodicoImmobiliare.ExcludedList
SELECT rete,codicefiscale,STRING_AGG( ISNULL(DescrErr, ' '), ',') As descrerr 
	into #riunisco_imm
       From #rimuovoduplicati_imm group by rete,codicefiscale
update C6MartPeriodico.tabellone 
set blacklist_imm=r.descrerr
from #riunisco_imm r inner join C6MartPeriodico.tabellone t 
on t.chiave_acn=r.Rete+r.codicefiscale
update C6MartPeriodico.tabellone 
set lettera_sost_imm='S'
where blacklist_imm like '%sostitutiva%'
end
if (@param1='SBLOCCO') 
begin
update C6MartPeriodico.tabellone 
set blacklist_imm=NULL
drop  table if exists #riunisco1_imm
drop table if exists #rimuovoduplicati1_imm
select distinct rete,codicefiscale,DescrErr
into #rimuovoduplicati1_imm
from  C6MartPeriodicoImmobiliare.ExcludedList
SELECT rete,codicefiscale,STRING_AGG( ISNULL(DescrErr, ' '), ',') As descrerr 
	into #riunisco1_imm
       From #rimuovoduplicati1_imm group by rete,codicefiscale
update C6MartPeriodico.tabellone 
set blacklist_imm=r.descrerr
from #riunisco1_imm r inner join C6MartPeriodico.tabellone t 
on t.chiave_acn=r.Rete+r.codicefiscale
update C6MartPeriodico.tabellone 
set lettera_sost_imm='S'
where blacklist_imm like '%sostitutiva%'
end
end