CODEWITHSUNDEEP
sqlsql-server
abhishek
abhishek

Reputation: 2992

Parameterized Query for Null

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.

Upvotes: 2

Views: 127

Answers (3)

Zeki Gumus
Zeki Gumus

Reputation: 1484

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)

Upvotes: 2

StepUp
StepUp

Reputation: 38199

check by is NULL:

DECLARE @tbl TABLE
(
   id INT,
   name VARCHAR(50),
   age INT null
)
INSERT INTO @tbl
(
    id,
    name,
    age
)
VALUES
(1, 'Abhishek', 30),
(2, 'Surajit',  NULL),
(3, 'Utpal',  44)

DECLARE @pAge INT = null
SELECT * FROM @tbl t
WHERE t.age IN (@pAge) OR (t.age IS NULL AND @pAge IS null)

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15150

If you want to select on NULL = NULL try:

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

Upvotes: 6

Related Questions