Apply and/or in SQL query based on flags passed to stored procedure

Question

I need a query which can apply and/or clauses dynamically basing on the flags. I am using SQL Server 2014.

Consider the following example:

declare @UGFlag char(1)
declare @PGFlag char(1)
declare @DoctrateFlag  char(1)

create table #FiltQual(Candidate_ID int) -- Final Output

create table #FiltUG(Candidate_ID int)
create table #FiltPG(Candidate_ID int)
create table #FiltDOC(Candidate_ID int)

insert into #FiltUG
select '1' union
select '2' union
select '3'

insert into #FiltPG
select '1' union
select '2'

insert into #FiltPG
select '2' union
select '3' union
select '4' union
select '5' 

--Case 1
set @UGFlag='Y'
set @PGFlag='Y'
set @DoctrateFlag = 'Y'
--Desired Output 
Candidate_ID
2

Case 2 
set @UGFlag='N'
set @PGFlag='N'
set @DoctrateFlag = 'N'
Desired Output 
Candidate_ID
1
2
3
4
5

Case 3: 
set @UGFlag='N'
set @PGFlag='Y'
set @DoctrateFlag = 'N'
Desired Output 
Candidate_ID
1
2

Case 4:
set @UGFlag='Y'
set @PGFlag='Y'
set @DoctrateFlag = 'N'
Desired Output 
Candidate_ID
1
2

I want to populate data into #FiltQual basing on the trhee flags. Flags can contain either 'Y' or 'N', if it is 'Y' then 'And' should be apllied else 'or' should be applied.

Answer 1

Something like this should work:

SELECT COALESCE(UG.Candidate_ID, PG.Candidate_ID, DOC.Candidate_ID) AS Candidate_ID
FROM #FiltUG AS UG
FULL OUTER JOIN #FiltPG AS PG ON UG.Candidate_ID = PG.Candidate_ID
FULL OUTER JOIN #FiltDOC AS DOC ON PG.Candidate_ID = DOC.Candidate_ID
WHERE ((@UGFlag='Y' AND UG.Candidate_ID IS NOT NULL) OR (@UGFlag='N'))
      AND
      ((@PGFlag='Y' AND PG.Candidate_ID IS NOT NULL) OR (@PGFlag='N'))
      AND
      ((@DoctrateFlag='Y' AND DOC.Candidate_ID IS NOT NULL) OR (@DoctrateFlag='N'))

The key idea is to use a FULL JOIN between all tables. Then using, for example:

(@UGFlag='Y' AND UG.Candidate_ID IS NOT NULL) OR (@UGFlag='N') 

we apply the NOT NULL condition to a table (#FiltUG) if the corresponding table filter (@UGFlag) is equal to 'Y.

Demo here