Skip to content
Advertisement

Avoiding IF statement to control WHERE statement

I have the below code which works and does what it needs to.

However, I need to manage two exact queries through an IF statement.

Can anyone see a way to do away with the IF ?

IF @ReportClientTypeAbbreviation = 'IA'
BEGIN
    select  
        *
    from 
        Client CR
    Inner Join LookUp.ClientType CT on 
        CR.ClientTypeID = CT.ClientTypeID
    where
        CR.SubmitDate >=@ReportStartDate and CR.SubmitDate <=@ReportEndDate and
        CT.ReportClientTypeAbbreviation = 'IA'
END
ELSE
BEGIN
    select  
        *
    from 
        Client CR
    Left Join LookUp.ClientType CT on 
        CR.ClientTypeID = CT.ClientTypeID
    where
        CR.SubmitDate >=@ReportStartDate and CR.SubmitDate <=@ReportEndDate and
        (CT.ReportClientTypeAbbreviation = 'NonIA' or CT.ReportClientTypeAbbreviation is null)
END

Advertisement

Answer

You can combine the queries using AND/OR logic which is essential knowledge for SQL queries. The following shows how to do this (untested – but should give you enough to go on):

select *
from Client CR
left join LookUp.ClientType CT on CR.ClientTypeID = CT.ClientTypeID
where CR.SubmitDate >= @ReportStartDate and CR.SubmitDate <= @ReportEndDate
and (
  (CT.ReportClientTypeAbbreviation = 'IA' and @ReportClientTypeAbbreviation = 'IA')
  or (
    @ReportClientTypeAbbreviation != 'IA' and (CT.ReportClientTypeAbbreviation = 'NonIA' or CT.ReportClientTypeAbbreviation is null)
  )
)
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement