SQL Server: How to write conditional WHERE clause containing different queries per condition?

Question

I found a bunch of answers on StackOverflow in regards to conditional WHERE clauses, but I wasn't able to find one that was somewhat specific to my issue, so just looking for a little help...

My company's website contains all SQL queries written directly within the C# code (I'm not a fan of that, but that's another story) & I'm having an issue trying to update a query that contains multiple conditions in the WHERE clause based on which checkboxes a user checks off on the form.

Within the form is a set of checkboxes (currently 3 options, but this list will potentially grow). For each "Provider Type" selected, an additional condition needs to be added to the WHERE clause but I'm unsure how to best handle this.

Here's the code that handles this. ProviderTypesCSV can contain up to 3 values (0,1,2) based on how many options the user checked:

if (!String.IsNullOrEmpty(searchOptions.ProviderTypesCSV))
{
    // values are in a csv string.  Split out the csv
    var statuses = searchOptions.ProviderTypesCSV.Split(',');

    if (statuses.Contains("0"))   //Hospitals & Facilities
    {
        strSQL += " AND (<<perform query for 'Hospitals & Facilities'>>)";
    }

    if (statuses.Contains("1"))  //Physicians and Practitioners
    {
        strSQL += " AND (<<perform query for 'Physcians and Practitioners'>>)";
    }

    if (statuses.Contains("2"))   //In-Person Visit Providers
    {
        strSQL += " AND (<<perform query for 'In-Person Visit Providers'>>)";
    }
}

The current issue obviously, is the above query doesn't take into account when multiple options are selected. When multiple options are selected, the conditional operator needs to be "OR", not "AND", but I'm not sure how to best set this up without making a mess & taking into account add'l options may be added later.

Just curious on the best way to set this up.

The truly best way to handle this is to stop writing sql in your programming code. It is sloppy, prone to error and brings many challenges that are unnecessary. One of the challenges you have here is you need to append OR for each predicate but you don't even know what other predicates might already be in place. Anything you do purely from code like this is a kludge waiting to fail. — Sean Lange
– Sean Lange, Commented Dec 14, 2021 at 14:14
@SeanLange - I 100% agree to your point! I joined this company a few months ago & brought this up (as I'm sure all new developers who come on board have). The response is always to keep the code as-is. We're in the early stages of a complete re-write of this website, however this existing one, sadly needs to be maintained in the meantime :( — goalie35
– goalie35, Commented Dec 14, 2021 at 14:19

Luca Riccitelli · Accepted Answer · 2021-12-14 14:20:58Z

You can define a List of string and add your conditions, then concat each element with an " OR " delimiter and use it for your AND

if (!String.IsNullOrEmpty(searchOptions.ProviderTypesCSV))
    {
        //values are in a csv string.  Split out the csv
        var statuses = searchOptions.ProviderTypesCSV.Split(',');
        
        List<string> conditions = new List<string>();

        if (statuses.Contains("0"))   //Hospitals & Facilities
        {
            conditions.Add("(<<perform query for 'Hospitals & Facilities'>>)");
        }
        if (statuses.Contains("1"))  //Physicians and Practitioners
        {
            sconditions.Add("(<<perform query for 'Physcians and Practitioners'>>)");
        }
        if (statuses.Contains("2"))   //In-Person Visit Providers
        {
            conditions.Add("(<<perform query for 'In-Person Visit Providers'>>)");
        }
        
        strSQL += " AND (" + String.Join(" OR ", list) + ") ";
        
    }

Collectives™ on Stack Overflow

SQL Server: How to write conditional WHERE clause containing different queries per condition?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related