Conditional WHERE based on SQL Server parameter value

Question

I am modifying an existing stored procedure in SQL server and trying to come up with a query that should take a conditional WHERE depending on whether one of the input parameters is null or has value.

The stored procedure takes three input parameters

@FromDate
@ToDate
@PersonnelNo

@FromDate and @ToDate will always have value and will be part of the WHERE condition but @PersonnelNo can be null or can hold value.

When @PersonnelNo is null I want all users for the given @FromDate and to @ToDate date but when the parameter @PersonnelNo has a value then I want the stored procedure to return data for that user only.

I’ve tried the below two approaches but it only works (gives one user record correctly) when I input value for @PersonnelNo. It doesn’t return any record if I input @PersonnelNo as null, rather I want all users.

Any idea where I’m going wrong or is that possible at all with the current approach I am taking?

SELECT  FirstName,
        LastName,
        PersonnelNum,
        RecCreatedOn
FROM    [test].Person
WHERE   RecCreatedOn BETWEEN @FromDate AND @ToDate
        AND (
         (ISNULL(@PersonnelNo, 0) != 0 AND PersonnelNum = @PersonnelNo)
        )

and a slightly different WHERE clause

WHERE   RecCreatedOn BETWEEN @FromDate AND @ToDate
        AND ((@PersonnelNo IS NOT NULL) AND PersonnelNum = @PersonnelNo)

JohnLBevan · Accepted Answer · 2014-07-15 07:32:20Z

6

This works by saying if @PersonnelNo is null the replace it with PeronnelNum. PersonnelNum will always equal PersonnelNum, so if @PersonnelNo is null this condition will always be true.

SELECT  FirstName,
        LastName,
        PersonnelNum,
        RecCreatedOn
FROM    [test].Person
WHERE   RecCreatedOn BETWEEN @FromDate AND @ToDate
AND     (@PersonnelNo is null or PersonnelNum = @PersonnelNo)

Explanation of your previous attempts:

ISNULL(@PersonnelNo, 0) != 0 AND PersonnelNum = @PersonnelNo

If @PersonnelNo is null this becomes:

0 != 0 and PersonnelNum = 0.
This becomes false and false
which becomes false
so no rows will be returned

If @PersonnelNo is (e.g.) 1 this becomes:

1 != 0 and PersonnelNum = 1.
This becomes true and true (for the row where PersonnelNum is 1)
which becomes true
so you will see the row for person 1.

(@PersonnelNo IS NOT NULL) AND PersonnelNum = @PersonnelNo

If @PersonnelNo is null this becomes:

(null is not null) and PersonnelNum = null.
This becomes false and false
which becomes false
so no rows will be returned

If @PersonnelNo is (e.g.) 1 this becomes:

1 is not null and PersonnelNum = 1.
This becomes true and true (for the row where PersonnelNum is 1)
which becomes true
so you will see the row for person 1.

Methods which do work:

PersonnelNum = coalesce(@PersonnelNo,PersonnelNum)
(@PersonnelNo is null or PersonnelNum = @PersonnelNo)

There's a minor difference between the methods above though. If PersonnelNum is nullable you may get different results; i.e. the coalesce method wouldn't include rows with a null value in this column whilst the second method will.

edited Jul 15, 2014 at 7:32

answered Jul 14, 2014 at 22:00

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dennis R Over a year ago

Great.. what if there is an exiting record with a null personnelNo and passing null for @PersonnelNo, will it return the user with null personnelNo?

JohnLBevan Over a year ago

That won't be an issue since (confusingly) null doesn't equal null (and also it doesn't not equal null). To explain, if you ask someone their age and they reply "I don't know" you can't say they're the same age as another person who says "I don't know", even though they gave the same answer to the question.

JohnLBevan Over a year ago

Here's some examples to better illustrate this: sqlfiddle.com/#!6/d41d8. If you're interested there's also a great article on many valued logic here, which is far more confusing than SQL's nulls: aeon.co/magazine/world-views/logic-of-buddhist-philosophy

JohnLBevan Over a year ago

No worries. FYI: better illustration of null comparisons here: sqlfiddle.com/#!6/8ee1f/3

Dennis R Over a year ago

Good..that explains it. I'll try it out and compare the results.

|

Collectives™ on Stack Overflow

Conditional WHERE based on SQL Server parameter value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related