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

Question

1.00/5 (1 vote)

See more:

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?

I tried many queries like

SQL

select * from Doctorslist where

case when @Name is not null
then DoctorName
end
=
case when @Name is not null
then @Name
end

and

case when @Country is not null
then Country
end
=
case when @Country is not null
then @Country
end

and

case when @State is not null
then State
end
=
case when @State is not null
then @State
end

but here if any parameter is null value it will throw errors.

How to use "=" symbol or like inside then statement

Thank you in ADVANCE

Posted 31-Jul-13 0:58am

Aboobakkar Siddeq D U

Add a Solution

Comments

Aboobakkar Siddeq D U 31-Jul-13 7:02am

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?

3 solutions

Solution 1

COL[^]
use COALESCE key word to handle null in where clause.

Posted 31-Jul-13 1:01am

Dheeraj_Gupta

Comments

Aboobakkar Siddeq D U 31-Jul-13 7:19am

I'm not asking for handle null value in where clause, i'm asking about condition inside where clause? How to add If condition inside Where clause or How to add ("=" or "like") inside 'then' statement in CASE ?

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-07-31T01:11:00

Solution 2

You can do this by using ISNULL Keyword in a Simple Select Statement...

SQL

select * from Doctorslist 
where DoctorName = isnull(@Name,DoctorName) 
and Country = isnull(@Country,Country)
and State = isnull(@State,State)

Posted 31-Jul-13 1:11am

Raja Sekhar S

Updated 31-Jul-13 1:13am

v2

Comments

Aboobakkar Siddeq D U 31-Jul-13 7:36am

Yes, I got this, But Can I use ("like isnull(@Name,DoctorName) '%' ") here?

Raja Sekhar S 31-Jul-13 7:46am

You cannot use like isnull(@Name,name)%
but while assigning the variable itself u can use @Name ='T%'

Aboobakkar Siddeq D U 31-Jul-13 7:37am

Thanks a lot

Raja Sekhar S 31-Jul-13 7:46am

You are Welcome...

Aboobakkar Siddeq D U 31-Jul-13 7:39am

Sorry now i'm not getting, because now i added all the conditions like

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where
DoctorName = isnull(@DoctorName,DoctorName)
and Country = isnull(@Country,Country)
and State = isnull(@State,State)
and Degree = isnull(@Degree,Degree)
and Speciality = isnull(@Speciality,Speciality)
and TelNumber = isnull(@TelNumber,TelNumber)
and District = isnull(@District,District)
and Email = isnull(@Email,Email)

but i'm not getting any data now? What is the wrong with this one?

Raja Sekhar S 31-Jul-13 7:46am

If there are Records Present Then u will get the records...

Aboobakkar Siddeq D U 31-Jul-13 7:48am

Now I have record with doctor name but not with Speciality, I should get doctor name if i enter only doctor names? How can I?

Raja Sekhar S 31-Jul-13 7:51am

In that case if u give @Specality variable as Null it will automatically come...

Adarsh chauhan 31-Jul-13 7:49am

Nice answer.. +5

Raja Sekhar S 31-Jul-13 7:50am

Thank You Adarsh...

Aboobakkar Siddeq D U 6-Aug-13 2:46am

Sorry Mr.Raja Sekhar S I want to try many conditions here, i'm getting error on this, my conditiong in like this


@DoctorName varchar(255),
@Degree varchar(255),
@Speciality varchar(255),
@TelNumber varchar(255),
@District varchar(255),
@State varchar(255),
@Country varchar(255),
@Email varchar(255)

AS

BEGIN

select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where 
DoctorName = isnull(@DoctorName,DoctorName)
and TelNumber = isnull(@TelNumber,TelNumber)
and State = isnull(@State,State) 
and Country = isnull(@Country,Country)
and Speciality = isnull(@Speciality,Speciality)

and Degree = isnull(@Degree,Degree)
and TelNumber = isnull(@TelNumber,TelNumber)
and District = isnull(@District,District)
and Email = isnull(@Email,Email)

END

Maciej Los · Accepted Answer · 2013-07-31T01:12:00

Solution 3

Try something like this:

SQL

--@WhereStatement should be in format:
-- [Name] = 'SomeName'
--or  
-- [Speciality] = 'SomSpeciality'
--and so on...
CREATE PROCEDURE usp_GetDoctorDetails
    @WhereStatement VARCHAR(300) NULL
AS
BEGIN
    DECLARE @sql VARCHAR(MAX)

    SET @sql = N'SELECT * FROM DoctorsList '
    IF (NOT @WhereStatement IS NULL) 
        SET @sql = @sql + @WhereStatement

    EXEC(@sql)
END

Use SearchBox[^] to get more details about dynamic queries.

Posted 31-Jul-13 1:12am

Maciej Los

Comments

Aboobakkar Siddeq D U 7-Aug-13 1:33am

How to write @WhereStatement here?

I tried this but this doesn't work
DECLARE @sql VARCHAR(MAX)
SET @sql = N'SELECT * FROM DoctorsList where'
IF (NOT @Name IS NULL)
SET @sql = @sql + 'DcotorName='+@Name

IF(not @Country is null)
set @sql=@sql+'and Country='+@Country

if(not @State is null)
set @sql=@sql+'and State='+@State

if(not @Speciality is null)
set @sql=@sql+'and Speciality='+@Speciality

EXEC(@sql)
END

Maciej Los 7-Aug-13 1:59am

"Doesn't work" is not informative at all ;(

Aboobakkar Siddeq D U 7-Aug-13 2:16am

Can you tell me what is the problem in my code here? Also i tried this

@Name Nvarchar(250),
@Country Nvarchar(250),
@State Nvarchar(250),
@Speciality Nvarchar(250),
@Email Nvarchar(250),
@Grade Nvarchar(250)

as

BEGIN

DECLARE @sql AS NVarchar(4000)

SET @sql = 'SELECT * FROM Doctorslist where (1=1)'

if @Name is not null
set @sql = @sql + ' And (DoctorName = @Name)'

if @Country is not null
set @sql = @sql+ ' And (Country = @Country)'

if @State is not null
set @sql = @sql + ' And (State = @State)'

if @Speciality is not null
set @sql = @sql + ' And (Speciality = @Speciality)'

EXEC(@sql)
END

Maciej Los 7-Aug-13 2:19am

Replace: if @Name is not null set @sql = @sql + ' And (DoctorName = @Name)'
with: if @Name is not null set @sql = @sql + ' And DoctorName =''' + @Name + ''''

Aboobakkar Siddeq D U 7-Aug-13 2:34am

Thanks a lot...........Mr. Maciej Los. I solved this with your solution thanks a lot.

Maciej Los 7-Aug-13 2:37am

You're welcome. It would be good to mark answer as "solved" (green button) to remove it from unanswered list. ;)

Aboobakkar Siddeq D U 7-Aug-13 2:44am

Can you tell me How Can I Add 'Like' condition to all conditions?

Maciej Los 7-Aug-13 2:46am

Not sure what you mean...

Aboobakkar Siddeq D U 7-Aug-13 2:52am

I mean i want to use query like this

if @DoctorName is not null
set @sql = @sql + ' And DoctorName like =''' + @DoctorName + '%'''

Can I use use like this?

I want to get records like first letter
Ex: select * from Doctorlist where DoctorName like @Doctorname + %

Maciej Los 7-Aug-13 2:56am

Yes, you can. Try this:
set @sql = @sql + ' And DoctorName Like =''' + @DoctorName + '''%'

Aboobakkar Siddeq D U 7-Aug-13 3:21am

Yes I tried, I got this by adding like this

set @sql = @sql + ' And DoctorName like ''' + @DoctorName + '%'''

Maciej Los 7-Aug-13 3:27am

Great!

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

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

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

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us