Conditional WHERE based on SQL Server parameter value

sql, sql-server, sql-server-2008

Solution

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.

Problem

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) ```

Original source