I am trying to select a query based on a parameter passed, but somehow the query does not work in all cases.
Lets say this is my query :
SELECT * FROM EMPLOYEE WHERE AGE = @pAge
Now let us consider this table
EmpId | EmpName | Age
1 | Abhishek | 30
2 | Surajit | NULL
3 | Utpal | 44
Now the problem is when I pass @pAge = NULL, it should select Surajit, and when I pass value it should select the value that matches specified. Looks simple but I am out of any option to try. The first query does not work because NULL != NULL.
Lets see what I have tried :
SELECT * FROM EMPLOYEE WHERE (@pAge is null Or AGE = @pAge)
This does not work as when I pass null it selects everything.
SELECT * FROM EMPLOYEE WHERE IsNull(@pAge, 0) = Age
But it does not work as well... because Age != 0.
Even I tried with case, but even it does not work correctly. Can anyone give me the simple query.
If you want to select on NULL = NULL try:
SELECT *
FROM EMPLOYEE
WHERE (
(@pAge IS NULL AND AGE IS NULL)
OR AGE = @pAge
)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments