SQL Execute one query of 2 based on condition

I have 2 working query that I need to use to generate a report, I have configured 4 parameters that user can choose and I want that if idarea=0 then first query is executed, else second one. I tried the following but give me an error because of the nested with… any help? Thanks

declare @DataDa as date='20210501';
declare @DataA as date='20210504';
declare @idUtilizzatore as int=0;
declare @idArea as int=0;
SELECT CASE WHEN ( @idArea=0 )
    THEN (select dbaree.dbo.StoricoAree.Descr as Lettore,
                                TIMBACCESSI.CODICEBADGE,
                                COGNOME as Cognome,NOME as Nome,
                                AZIENDE.DESCR as Azienda,FORMAT(DATAORA,'HH:mm') as Ora,
                                FORMAT(dataora,'dd/MM/yyyy') as Data, 
                                iif(verso=0,'U','E') as Flusso from timbaccessi 
                                join badge on badge.codice=timbaccessi.codicebadge 
                                join anagrafico on anagrafico.ID=badge.IDUTILIZZATORE 
                                join profilo on profilo.IDUTILIZZATORE=badge.IDUTILIZZATORE 
                                join aziende on aziende.id=profilo.IDAZIENDA 
                                join terminali on terminali.id=TIMBACCESSI.IDTERMINALE 
                                join comparee on terminali.id=COMPAREE.IDTERMINALE 
                                join dbAree.dbo.StoricoAree on storicoaree.idArea=COMPAREE.IDAREA 
                                where (DATAORA>=@DataDa and DATAORA<@DataA) and (DATAORA >= dataDa and(DATAORA <= dataA or dataa is null)) and (badge.DATAFINE is null or badge.DATAFINE>=@DataA) AND (badge.idUtilizzatore = @IdUtilizzatore OR 0 = @IdUtilizzatore) AND (PROFILO.IDAZIENDA=@idAzienda OR 0 = @idAzienda) and (dbaree.dbo.StoricoAree.id=@idArea OR 0=@idArea)
                                group by dataora,cognome,nome,dbaree.dbo.StoricoAree.Descr,timbaccessi.CODICEBADGE,aziende.DESCR,timbaccessi.VERSO
                                ORDER BY Azienda asc, cognome asc, nome asc, Data asc, ora asc)
    ELSE (with area as(


                                select t.CODICEBADGE as 'Numero tessera',
                                COGNOME as Cognome,NOME as Nome,
                                AZIENDE.DESCR as Azienda,FORMAT(DATAORA,'HH:mm') as Ora,
                                FORMAT(dataora,'dd/MM/yyyy') as Data, 
                                iif(verso=0,'U','E') as Flusso, t.IDTERMINALE,t.DATAORA from timbaccessi t 
                                join badge on badge.codice=t.codicebadge 
                                join anagrafico on anagrafico.ID=badge.IDUTILIZZATORE 
                                join profilo on profilo.IDUTILIZZATORE=badge.IDUTILIZZATORE 
                                join aziende on aziende.id=profilo.IDAZIENDA 
                                join terminali on terminali.id=t.IDTERMINALE 
                                where (DATAORA>=@DataDa and DATAORA<@DataA) 
                                and (badge.DATAFINE is null or badge.DATAFINE>=@DataA) AND (badge.idUtilizzatore = @IdUtilizzatore OR 0 = @IdUtilizzatore)
                                group by dataora,cognome,nome,t.CODICEBADGE,aziende.DESCR,t.VERSO,t.IDTERMINALE)
                                select a.[Numero tessera],a.Cognome,a.Nome,a.Azienda,a.Ora,a.Data,a.Flusso,s.Descr from area a join dbAree.dbo.StoricoAree s on s.id=@idArea where a.IDTERMINALE in (select idterminale from dbAree.dbo.termArea t join dbaree.dbo.StoricoAree s on s.id=t.idArea where t.idArea=@idArea and (@DataDa >= s.datada and (s.dataA is null or @DataDa < s.dataA))) 
                                order by a.DATAORA )
END;

Answer

Just use IF:

IF (@idArea = 0)
BEGIN
    <query1>;
END;
ELSE 
    <query 2>;

Advertisement

Answer