Click here to Skip to main content
14,271,512 members
Rate this:
Please Sign up or sign in to 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

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
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?
Rate this:
Please Sign up or sign in to vote.

Solution 2

You can do this by using ISNULL Keyword in a Simple Select Statement...
select * from Doctorslist 
where DoctorName = isnull(@Name,DoctorName) 
and Country = isnull(@Country,Country)
and State = isnull(@State,State)
   
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

COL[^]
use COALESCE key word to handle null in where clause.
   
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 ?
Rate this:
Please Sign up or sign in to vote.

Solution 3

Try something like this:
--@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.
   
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!

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100