1

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 ?

David
  • 4,332
  • 13
  • 54
  • 93
  • Assuming we're talking about MS SQL Server, you cannot compare a column value with `NULL` using `=`... you have to use either `IS NULL` or `IS NOT NULL` – freefaller Oct 11 '18 at 09:08
  • Why don't build query dynamically? Just throw away `WHERE ADB.param1=param` from query, when `param1` is null. – Dennis Oct 11 '18 at 09:09
  • can you write this part of code ? I mean check where ADB.param1=@param if @param is not null – David Oct 11 '18 at 09:09
  • `where (ADB.param1=@param or @param is null)` – jarlh Oct 11 '18 at 09:10
  • @David - do you want the query to return ALL rows when `@param` is null... or only return rows where `param1` is null? – freefaller Oct 11 '18 at 09:11
  • Possible duplicate of [this](https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure). – Han Oct 11 '18 at 09:11
  • Specify initial null value for `@param` as optional parameter should work, e.g. `@param [datatype] = NULL`. Or just use `ISNULL(@param)`. – Tetsuya Yamamoto Oct 11 '18 at 09:15

3 Answers3

2

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)
freefaller
  • 19,368
  • 7
  • 57
  • 87
  • in such case I get all rows with ADB.param1 equaled to "0" but I want it to be what is stored in DB rows – David Oct 11 '18 at 09:25
  • Sorry @David, I'm not sure I understand your comment. Are you saying my answer doesn't do what you need, or are you clarifying that it does? – freefaller Oct 11 '18 at 09:28
  • where ((@param1 is null and ADB.param1 is null) or ADB.param1 = @param1) gives all rows but ADB.param1 is shown as "0" in each row. Instead of that I want to get all rows with corresponding ADB.param1 when @param1 is null – David Oct 11 '18 at 09:30
  • @David, ok, try this: `where ((@param1 is null and ADB.param1 is null) or (@param1 is not null and ADB.param1 = @param1))` – freefaller Oct 11 '18 at 09:32
  • thanks for the helped but I went with Tim Biegeleisen solution. It worked fine. Thanks anyway! – David Oct 11 '18 at 09:33
1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

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

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