How to write If condition inside where clause in SQL Queries?

sql if statement in where clause

I want to write a storedprocedure to get doctor details from Doctorlist table. where it check for eight conditions like (Name, Speciality, Grade, Country, State, District, Telephone Number, Email). Here i'm passing 8 parameter to stored procedure. How can I write a query to get doctor details using these conditions in a short way?

if(@DoctorName is not NULL and @Speciality is not NULL and @State is not NULL and @Country is not NULL and @TelNumber is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where

DoctorName like @DoctorName + '%' and Speciality like @Speciality + '%' and State like @State + '%' and Country like @Country + '%'

else if(@DoctorName is not null and @Speciality is not null and @State is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where

DoctorName like @DoctorName + '%' and Speciality like @Speciality + '%' and State like @State + '%'

else if(@DoctorName is not null and @Speciality is not null and @Country is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where

DoctorName like @DoctorName + '%' and Speciality like @Speciality + '%' and Country like @Country + '%'

else if(@DoctorName is not null and @State is not null and @Country is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where

DoctorName like @DoctorName + '%' and State like @State + '%' and Country like @Country + '%'

else if(@Speciality is not null and @State is not null and @Country is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where

Speciality like @Speciality + '%' and State like @State + '%' and Country like @Country + '%'

else if(@DoctorName is not null and @Speciality

is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where DoctorName like @DoctorName + '%' and Speciality like @Speciality + '%'

else if(@DoctorName is not null and @State is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where DoctorName like @DoctorName + '%' and State like @State + '%'

else if(@DoctorName is not null and @Country is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where DoctorName like @DoctorName + '%' and Country like @Country + '%'

else if(@Speciality is not null and @State is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where Speciality like @Speciality + '%' and State like @State + '%'

else if(@Speciality is not null and @Country is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where Speciality like @Speciality + '%' and Country like @Country + '%'

else if(@State is not null and @Country is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where State like @State + '%' and Country like @Country + '%'

else if(@DoctorName is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where DoctorName like @DoctorName + '%'

else if(@Speciality is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where Speciality like @Speciality + '%'

else if(@State is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where State like @State + '%'

else if(@Country is not null)

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where Country like @Country + '%'

THIS IS THE QUERY I USED TO GET DETAILS, HERE I USED ONLY 4 CONDITIONS (STATE, COUNTRY, SPECIALTY AND DOCTORNAME) SO I GOT 15 MORE QUERIES, BUT IF I WRITE ALL CONDITIONS IT WILL BECOME 255 MORE QUERIES, SO IS THERE ANY OTHER WAY TO GET IT?

Source: www.codeproject.com

Category: Forex

Similar articles: