CODEWITHSUNDEEP
c#linqt-sql
Daniela
Daniela

Reputation: 657

SQL Query with multiple parameters that could be empty strings

I am running a query and there are multiple parameters that can be used to filter the records returned. The parameters are coming from dropdown menus, so if the user doesn't choose an option, the parameter comes over in an empty string.

How can I ignore the parameters that are a ""?

Here is the query: (There might be a better way to do this in LINQ, but my SQL skills are stronger). I am building this report in MVC 5/C#.

approvalReport = (from td in db.TData
                              from call in
                                  (from x in db.CallLog
                                   where x.TID == td.TID
                                   orderby x.CallDate descending
                                   select new
                                   {
                                       x.CallStatus,
                                       x.CallNotes,
                                       x.CallDate
                                   }).Take(1).DefaultIfEmpty()
                              from appt in
                                  (from x in db.Appt
                                   where x.TID == td.TID
                                   orderby x.ApptCreateDate descending
                                   select new
                                   {
                                       x.ApptStatus,
                                       x.ApptDate
                                   }).Take(1).DefaultIfEmpty()
                              where (td.Location == location && td.ApprovedBy == approvedBy && td.PCP == pcp && td.IPA == ipa)
                              orderby td.MemberName descending
                              select new ApprovalDataVM()
                              {
                                  MemberName = td.MemberName,                                      
                                  PCP = td.PCP,
                                  IPA = td.IPA,
                                  Location = td.Location,
                                  ApprovedBy = td.ApprovedBy,
                                  CallStatus = call.CallStatus,
                                  ApptDate = appt.ApptDate,
                                  ApptStatus = appt.ApptStatus,
                                  callLogCt = db.CallLog.Where(x => x.TID == td.TID).Count(),
                              });

I have done some research on this and tried this for the where clause, but it didn't work. The report didn't pull any data. The example below is abbreviated to 3 parameters, and "approvedBy" will never be null or "".

where (td.ApprovedBy == approvedBy && (pcp =! "' && td.PCP == pcp) && (ipa != "" && td.IPA == ipa)

I don't want to have to repeat myself for all the parameter combinations that are possible. What is the best solution for this?

The CallLog and Appt tables have a foriegn key of the td.TID. The output will be a list displaying all of the data in the view model.

View:

 <div class="table-responsive">
        <table class="table table-hover small">
            <tr>
                <th></th>
                <th>Approved By</th>
                <th>Member Name</th>
                <th>DOB</th>
                <th>PCP</th>
                <th>IPA</th>
                <th>Call Status</th>
                <th>Call Count</th>
                <th>Appt Date</th>
                <th>DOS</th>
            </tr>
            @{ int counter = 0; }
            @foreach (var item in Model.reportData)
            {
                counter = counter + 1;
                <tr>
                    <td>
                        @counter
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.ApprovedBy)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.MemberName)
                    </td>   
                    <td>
                        @if (item.DateOfBirth != null)
                        {
                            @item.DateOfBirth.Value.ToShortDateString()
                        }
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.PCP)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.IPA)
                    </td>
                    <td>@Html.DisplayFor(modelItem => item.CallStatus)</td>
                    <td>@Html.DisplayFor(modelItem => item.callLogCt)</td>
                    <td>@Html.DisplayFor(modelItem => item.ApptStatus)</td>
                    <td>
                        @if (item.ApptDate != null)
                        {
                            @item.ApptDate.Value.ToShortDateString()
                        }
                    </td>

                </tr>
            }
        </table>

Form that feeds the search query:

<form class="form-inline" role="form">                                      
                    <div class="form-group" style="padding-right:15px;">
                        <label class="control-label">*Approved By: &nbsp;</label>
                        @Html.DropDownList("approvedBy", Model.ApprovedByList, "Select One", new { @class = "input-sm" })
                        @Html.ValidationMessageFor(model => model.ApprovedByList, "", new { @class = "text-danger" })
                    </div>
                    <div class="form-group" style="padding-right:15px;">
                        <label class="control-label">*Location: &nbsp;</label>
                        @Html.DropDownList("location", (IEnumerable<SelectListItem>)ViewBag.location, "Select One", new { @class = "input-sm" })
                    </div>  
                    <input type="submit" value="Run Report" class="btn-primary btn-sm" />
                    <hr />
                    <div class="form-group" style="padding-right:15px;">
                        <label class="control-label">PCP: &nbsp;</label>
                        @Html.DropDownList("pcp", Model.PCPList, "All", new { @class = "input-sm" })
                    </div>
                    <div class="form-group" style="padding-right:15px;">
                        <label class="control-label">IPA: &nbsp;</label>
                        @Html.DropDownList("ipa", Model.IPAList, "All", new { @class = "input-sm" })
                    </div>
                    <div class="form-group" style="padding-right:15px;">
                        <label class="control-label">Call Status: &nbsp;</label>
                        @Html.DropDownList("callStatus", Model.CallStatusList, "All", new { @class = "input-sm" })
                    </div>
                    <br /><br />
                </form>

Upvotes: 0

Views: 302

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

You could do this

 where (td.ApprovedBy == approvedBy && td.Location == location && (pcp == "" || td.PCP == pcp) && (ipa == "" || td.IPA == ipa))

Upvotes: 2

Chris Arbogast
Chris Arbogast

Reputation: 123

I think you would actually want this to get the results you intended. My assumption is that if the user leaves a parameter blank you want that to match all rows, in other words, to not filter by that parameter.

td.ApprovedBy == approvedBy && (pcp = "" || td.PCP == pcp) && (ipa == "" || td.IPA == ipa)

Upvotes: 2

Related Questions