CODEWITHSUNDEEP
sqlsql-server
user1181942
user1181942

Reputation: 1607

Where clause in sql

My sql query is as follows

IF @StatusId = 10
    BEGIN
        SELECT 
            *
        FROM 
        Risk AS R
        INNER JOIN Statuses AS St ON R.Status_Id=St.Status_Id
        WHERE
        R.MitigationOwner = COALESCE(@MitigationOwner,R.MitigationOwner)
        AND R.RiskFactor = COALESCE(@RiskFactor,R.RiskFactor)
        AND R.RiskArea = COALESCE(@RiskArea,R.RiskArea)
        AND R.AddedWhen BETWEEN 
        COALESCE(CONVERT(DATETIME, @StartDate+'00:00:00',120),R.AddedWhen) AND 
        COALESCE(CONVERT(DATETIME,@EndDate+'23:59:59',120),R.AddedWhen)
    END

When I pass only status Id and all other variables are null, then records with NULL MitigationOwner or ModifiedDate are not displayed.. What is wrong in this query?

Upvotes: 0

Views: 196

Answers (3)

Paul Williams
Paul Williams

Reputation: 17020

If R.MitigationOwner can be null, then your comparison clause:

WHERE
R.MitigationOwner = COALESCE(@MitigationOwner,R.MitigationOwner)

Must be rewritten to handle NULL values:

WHERE
((R.MitigationOwner IS NULL AND @MitigationOwner IS NULL)
 OR (R.MitigationOwner = @MitigationOwner))

See this article on Wikipedia about NULL.

Upvotes: 1

gbn
gbn

Reputation: 432180

Use the form:

...
(R.MitigationOwner = @MitigationOwner OR  @MitigationOwner IS NULL)
...

This is optimised in SQL Server. COALESCE isn't.

Edit: This does the same as Paul Williams' answer but his answer allows explicit "NULL = NULL" matches. m ylogic is simpler because NULL never equals NULL.

Upvotes: 4

Euclides Mulémbwè
Euclides Mulémbwè

Reputation: 1687

I believe that by ModifiedDate you meant R.AddedWhen

try this:

SELECT 
            *
        FROM 
        Risk AS R
        INNER JOIN Statuses AS St ON R.Status_Id=St.Status_Id
        WHERE
        (R.MitigationOwner = COALESCE(@MitigationOwner,R.MitigationOwner) OR R.MitigationOwner IS NULL)
        AND R.RiskFactor = COALESCE(@RiskFactor,R.RiskFactor)
        AND R.RiskArea = COALESCE(@RiskArea,R.RiskArea)
        AND (R.AddedWhen BETWEEN 
        COALESCE(CONVERT(DATETIME, @StartDate+'00:00:00',120),R.AddedWhen) AND 
        COALESCE(CONVERT(DATETIME,@EndDate+'23:59:59',120),R.AddedWhen) OR R.AddedWhen IS NULL)

Upvotes: 1

Related Questions