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
How to Have Multiple Pivots Using the Same Pivot Column Using SQL Server
Bigquery - JSON_Extract All Elements from an Array
Psycopg2 Equivalent of MySQLdb.Escape_String
Unique Constraint on Combination of Two Columns
Check Constraint on Date of Birth
Recursive Subquerying with Sorting
In General, Should Every Table in a Database Have an Identity Field to Use as a Pk
Oracle Db: How to Write Query Ignoring Case
SQL Use Case Statement in Where in Clause
Lost the Intellisense in SQL Server Management Studio
Any Reason for Group by Clause Without Aggregation Function
Show Create Table Tablename in SQL Server
Split a Single Column of Data with Comma Delimiters into Multiple Columns in Ssis
Rails Active Query Order by Multiple Values in Specific Order