SQL Statement - How to write a WHERE clause with wildcard

Viewed 115

I have a C# application that includes a SQL query that includes this WHERE clause:

WHERE 
    A.Location = @Location
    AND Widgets.Branch = @Branch

The requirements have changed such that users can ask to see ALL branches. I know I can write an if statement to see if I have a branch parameter that's been passed in... and then have a different select statement that omits the AND clause for the branch altogether.

But I'm wondering if there's a simpler way to do this? Could I pass in some sort of a wildcard?

I tried '*' or '%' but that doesn't seem to be the right syntax. I know % is used with the LIKE...

Any suggestions / tips would be appreciated.

EDIT 1

So I'm trying to figure out how to use the

WHERE 
    A.Location = @Location
    AND (@Branch IS NULL OR Widgets.Branch = @Branch)

approach.

My code in part looks like this:

 using (var conn = new SqlConnection(connectionString))
 {
      activityByUserType = conn.Query<ActivityDetailsByUserTypes>(
      @"
                SELECT 
                    ReportRefreshDate,
                    other columns...
                FROM 
                    (SELECT
                         columns
                     FROM table1 A
                     INNER JOIN Widgets ON LOWER(fieldAA) = LOWER(A.fieldAA)
                     WHERE 
                         A.Location = @Location
                         AND Widgets.Branch = @Branch) DetailedResults
                 GROUP BY ReportRefreshDate;
                ",
                new { Branch=branchId, Location = LocationId, RefreshDate = dateRange}).ToList();
        }
        return activityByUserType;

As you can see, I'm creating a new query object ... and passing a list of parameters to it at the end of the code block.

I changed the logic to add this if statement before the USING()

if (branchId.Equals("allbranches")) 
{
   branchId = null;
}

But that doesn't seem to really work.

2 Answers

You could check if the parameter is null in the WHERE clause:

    WHERE 
        A.Location = @Location
    AND
        (@Branch IS NULL OR Widgets.Branch = @Branch)

This won't filter the branch when @Branch parameter is null.

You could explicitly check for an asterisk in the query itself:

@Branch IN ('*', Widgets.Branch)

This way, if you pass @Branch=*, the column is ignored, and otherwise it's compared to the value of the column.

Related