Parameterized Query for Null

Question

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.

Answer 1

If you want to select on NULL = NULL try:

SELECT * 
FROM   EMPLOYEE 
WHERE (
         (@pAge IS NULL AND AGE IS NULL)
      OR AGE = @pAge
      )

Answer 2

You can also try to convert both of them to 0 (or whatever you want) :

SELECT  * 
FROM    EMPLOYEE 
WHERE   ISNULL(@pAge, 0) = ISNULL(Age,0)