SQL Ignore Part of Where If Parameter Is Null

SQL ignore part of WHERE if parameter is null

How about something like

SELECT Id, col1, col2, col3, col4 
FROM myTable
WHERE col1 LIKE @Param1+'%'
OR @Param1 IS NULL

in this specific case you could have also used

SELECT Id, col1, col2, col3, col4 
FROM myTable
WHERE col1 LIKE ISNULL(@Param1,'')+'%'

But in general you can try something like

SELECT Id, col1, col2, col3, col4 
FROM myTable
WHERE (condition1 OR @Param1 IS NULL)
AND (condition2 OR @Param2 IS NULL)
AND (condition3 OR @Param3 IS NULL)
...
AND (conditionN OR @ParamN IS NULL)

Sql Server Ignore search on a field if parameter is null

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)

Ignore parameter in WHERE clause if it is set to null

Since there are not any nulls in your columns you can use COALESCE() like this:

SELECT * 
FROM tablename
WHERE c1 = COALESCE(@p1, c1)
AND c2 = COALESCE(@p2, c2)
AND c3 = COALESCE(@p3, c3)
AND c4 = COALESCE(@p4, c4)

SQL - ignore where clause if null / no input

This is typically handled by doing:

WHERE . . . AND
(@Integer IS NULL OR tb_Table_Integer = @Integer)

Do not use IN (@Integer). It sort of implies that you think that @Integer could be a list. That is not possible.

How to ignore NULL in WHERE clause when SELECT is used?

To address your specific issue of dealing with null values, you can simply use isnull

SELECT *
FROM MyTable
WHERE PartNum = (SELECT isnull(Value, PartNum) FROM #Variables WHERE VarName = 'PartNum')
AND PartColor = (SELECT isnull(Value, PartColor) FROM #Variables WHERE VarName = 'PartColor')


Related Topics



Leave a reply



Submit