(pass "all" in parameter ?) | how can I make parameter ? in WHERE filter to be a return of-CodePudding

I am making a wpf C# app following MVVM. It processes data from accdb (with several connected tables) using DataSet via ServerExplorer. I am trying to make a filtered search right now.

        SELECT        IDBug, [Date], ReportedBy, Replicated, ReproducableSteps, AppArea, Status, FixedVer, FixedBy, Notes, Title
        FROM            Bugs
        WHERE      ([Date] >=  ?)
        AND ([Date] <= ?) 
        AND (Status = ?)
        AND (AppArea = ?)
        AND (Replicated = ?)
        AND (ReportedBy =  ?)

This was working, but only if use all the filter settings.

        int idAr = (from DataRow dr in ars.GetData().Rows
                                        where (string)dr["Area"] == AreaSe.ToString()
                                        select (int)dr["IDArea"]).FirstOrDefault();
        

        int idCust = (from DataRow dr in custs.GetData().Rows
                                          where (string)dr["CustomerName"] == CustomS.ToString()
                                          select (int)dr["IDCustomer"]).FirstOrDefault();


        BugTable = bugs.GetT(StartDate, StopDate, State, idAr, replicationS, idCust);

I do have some null values in my data, that is why I tried to do smth similar to

WHERE Title = ? OR (? IS NULL))

instead of

WHERE Title = ?

for each of the parameters. But it messes up the data types...

I don`t think that is smart to write a different sql query for each case, so I need to know how cann I pass "SELECT ALL" to a ? parameter.

CodePudding user response：

You can do use case in where clause like this. You can pass null as default value for the parameter.

So it will return the records for @param if it is not passed else it will return all records.

SELECT Col1, Col2….
FROM TableName 
WHERE ColName = CASE WHEN @param IS NULL THEN ColName ELSE @param END