CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

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

Upvotes: 0

Views: 58

Answers (1)

Dale K
Dale K

Reputation: 27225

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)
  )
)

Upvotes: 4

Related Questions