CODEWITHSUNDEEP
c#sqlif-statementnullwhere-clause
David
David

Reputation: 4508

get all rows from db if parameter added to where clause is null

I have big select with where condition. 

WHERE ADB.param1=@param

In Where I send param variable

        SqlParameter param1Param = cmd.Parameters.AddWithValue("@param1", param1);
        if (param1== null)
        {
            param1Param.Value = DBNull.Value;
        }

This code works the following way. When query executes it always checks for ADB.param1=param condition and when I send DBNull.Value it gets nothing from database. But instead of that I want that

if param== null

then it pay no attantion to this condition and get all rows from database. How can I achive that ?

Upvotes: 1

Views: 1282

Answers (3)

freefaller
freefaller

Reputation: 19963

If you want to return all rows where the @param1 parameter is null use...

where (@param1 is null or ADB.param1 = @param1)

If you want to return only those rows where the column value of param is null use...

where ((@param1 is null and ADB.param1 is null) or ADB.param1 = @param1)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522119

Here is one trick which doesn't even require changing your C# code:

WHERE ADB.param1 = COALESCE(@param, ADB.param1)

For non SQL NULL values, the @param must equal the ADB.param1 column. Otherwise, the WHERE clause would always be true, and would return all records.

Upvotes: 1

user10457431
user10457431

Reputation:

I assume your query is WHERE ADB.param1=@param1

Try with WHERE (ADB.param1=@param1 OR @param1 IS NULL)

Upvotes: 0

Related Questions