SQL statement filter result

Question

I need to filter the SQL query result according to 3 conditions:
1. Location
2. Doctor Name
3. Specialty Name

Below is the sql query if all 3 conditions are not empty:

if (location != "" && doctor!="" && specialty!="")

select Location, ...
from clinicdoctors
where Location = @Location and DoctorName = @DoctorName and SpecialtyName = @SpecialtyName

}

if only location is empty,

if (location == "" && doctor!="" && specialty!="")
select Location, ...
from clinicdoctors
where Location is not null and DoctorName = @DoctorName and SpecialtyName = @SpecialtyName

...

If I wanna check all the conditions, I need to write eight if statements. What should I do to simplify the code in this situation?

Answer 1

I think it would be better to do this in the code than sql as you have done. Not really any way around it since the queries are so different, but a terse way to do it would be:

$loc_where = empty($loc) ? 'Location IS NOT NULL' : "Location = $loc";
$doc_where = empty($doc) ? 'AND DoctorName IS NOT NULL' : "AND DoctorName = $doc";
$spec_where = empty($spec) ? 'AND SpecialtyName IS NOT NULL' : "AND SpecialtyName = $spec";

query ... WHERE $loc_where $doc_where $spec_where

Answer 2

select Location, ...
from clinicdoctors
where 
 ISNULL(@Location,Location) = Location
 and ISNULL(@DoctorName,DoctorName) = DoctorName
 and ISNULL(@SpecialtyName,SpecialtyName) = SpecialtyName