CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Abhilash Srivastava
Abhilash Srivastava

Reputation: 85

Sql Server Ignore search on a field if parameter is null

I am creating a stored procedure
spRegularSearch having parameters 

@sex varchar(1),  
@maritalStatus varchar(20) = null,  
@minage int  = null,  
@maxage int = null,  
@state varchar(50) = null,  
@city varchar(50) = null

If all the parameters are not null my code is 

Select tblQuickRegister.memberId , tblUserLogin.lastLogin , tblQuickRegister.dob,tblPhysicalAttributes.height,
 tblHomeTruth.religion, tblEducation.highestQualification , tblOccupation.occupation, tblPicture.profilePic1
 from tblQuickRegister full outer join tblUserLogin on tblQuickRegister.memberId = tblUserLogin.memberId
 full outer join tblPhysicalAttributes on tblQuickRegister.memberId = tblPhysicalAttributes.memberId
 full outer join tblHomeTruth on tblQuickRegister.memberId = tblHomeTruth.memberId
 full outer join tblEducation on tblQuickRegister.memberId = tblEducation.memberId 
 full outer join tblOccupation on  tblQuickRegister.memberId = tblOccupation.memberId 
 full outer join tblPicture on tblQuickRegister.memberId = tblPicture.memberId 
 full outer join tblMaritalStatus on tblQuickRegister.memberId = tblMaritalStatus.memberId 
 full outer join tblContact on tblQuickRegister.memberId = tblContact.memberId 
 where
 tblQuickRegister.sex = @sex
 And tblMaritalStatus.maritalStatus = @maritalStatus 
 And (DATEDIFF(DAY,Convert(date,tblQuickRegister.dob),getdate())/365 >= @minage)
 And (DATEDIFF(DAY,Convert(date,tblQuickRegister.dob),getdate())/365 <= @maxage)
 And tblContact.[state] = @state 
 And tblContact.city = @city

I want to remove search on basis of field i.e. ignore [fieldname] = @parameter if @parameter is null
Of course i can achieve this using many if else conditions but is there any easy way to do it?

Upvotes: 0

Views: 3812

Answers (2)

FLICKER
FLICKER

Reputation: 6683

This is a common and duplicate question. You can use below: add "OR @parameter is null" to all your criteria

Select tblQuickRegister.memberId , tblUserLogin.lastLogin , tblQuickRegister.dob,tblPhysicalAttributes.height,
 tblHomeTruth.religion, tblEducation.highestQualification , tblOccupation.occupation, tblPicture.profilePic1
 from tblQuickRegister full outer join tblUserLogin on tblQuickRegister.memberId = tblUserLogin.memberId
 full outer join tblPhysicalAttributes on tblQuickRegister.memberId = tblPhysicalAttributes.memberId
 full outer join tblHomeTruth on tblQuickRegister.memberId = tblHomeTruth.memberId
 full outer join tblEducation on tblQuickRegister.memberId = tblEducation.memberId 
 full outer join tblOccupation on  tblQuickRegister.memberId = tblOccupation.memberId 
 full outer join tblPicture on tblQuickRegister.memberId = tblPicture.memberId 
 full outer join tblMaritalStatus on tblQuickRegister.memberId = tblMaritalStatus.memberId 
 full outer join tblContact on tblQuickRegister.memberId = tblContact.memberId 
 where
 (tblQuickRegister.sex = @sex or @sex is null)
 And (tblMaritalStatus.maritalStatus = @maritalStatus or @maritalStatus is null)
 And ((DATEDIFF(DAY,Convert(date,tblQuickRegister.dob),getdate())/365 >= @minage) or @minage is null)
 And ((DATEDIFF(DAY,Convert(date,tblQuickRegister.dob),getdate())/365 <= @maxage) or @maxage is null)
 And (tblContact.[state] = @state or @state is null)
 And (tblContact.city = @city or @city is null)

Upvotes: 3

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

One easy way to achieve this would be to add lookup conditions to your WHERE clause, which would evaluate to true thus not having an effect on the output if your @parameter value is null.

WHERE CASE WHEN @parameter IS NOT NULL THEN column = @parameter ELSE true END

I wouldn't actually recommend this approach, as the optimizer has no idea at planning time whether the @parameter will or will not be a null value. A better approach would be to build your query dynamically based on your parameters and append condition to your WHERE clause if it's relevant (parameter is not null).

Latter approach would certainly improve performance of execution.

Upvotes: 1

Related Questions