Multiple optional WHERE Parameters

Question

I'd like to create a filter for a set of SQL queries so the user may filter the SQL SELECT by up to three values. The user interface has three textboxes, each will tie to a column name in a SQL table. The user may supply one, two or three criteria via these textboxes.

Here's what I have so far. I know the if(textbox... statements won't work, but I cannot find a way to do this. (Using "SELECT TOP 10 primaryFile FROM dbo.basket WHERE (basket.itemGuid = @itemguid) AND (basket.batchid = @batchid) AND (basket.account = @account" won't return ANY results.

private List<string> GetSnippets()
{
    List<string> snippets = new List<string>();

    string connectionString = @"SNIP";
    //string sql = @"SELECT TOP 10 primaryFile FROM dbo.basket WHERE";
    string sql = @"SELECT TOP 10 primaryFile FROM dbo.basket WHERE (basket.itemGuid = @itemguid) AND (basket.batchid = @batchid) AND (basket.account = @account)";
    //if (textBoxGUID.Text.Length > 0) sql += " basket.itemGuid = @itemguid";
    //if (textBoxBatchID.Text.Length > 0) sql += " basket.batchid = @batchid";
    //if (textBoxAccount.Text.Length > 0) sql += " basket.account = @account";

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@itemguid", textBoxGUID.Text);
        command.Parameters.AddWithValue("@batchid", textBoxBatchID.Text);
        command.Parameters.AddWithValue("@account", textBoxAccount.Text);

        try
        {
            connection.Open();
            if (connection.State == ConnectionState.Open)
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        snippets.Add((string)reader["primaryFile"]);
                        Console.WriteLine(reader["primaryFile"]);
                    }
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
    }
    return snippets;
}

possible duplicate of [Stored Procedure with optional "WHERE" parameters](http://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters) — Daniel Kelley, Jun 30 '14 at 15:36
[Check out this Answer very well answered](https://stackoverflow.com/questions/50048397/what-is-best-way-to-search-from-optional-parameter-to-sql-query-in-a-stored-proc) — GaneshK, Apr 27 '18 at 15:08

score 4 · Answer 1 · answered Jun 30 '14 at 15:36

I would keep the where clause separate and append to it as needed.

string sql = @"SELECT TOP 10 primaryFile FROM dbo.basket ";
string where = "";
if (textBoxGUID.Text.Length > 0) 
{
    if(where.Length > 0 ) where += "AND "
    where += " (basket.itemGuid = @itemguid) ";
}
if (textBoxBatchID.Text.Length > 0) 
{
    if(where.Length > 0 ) where += "AND "
    where += " (basket.batchid = @batchid) ";
}
if (textBoxAccount.Text.Length > 0) 
{
    if(where.Length > 0 ) where += "AND "
    where += " (basket.account = @account) ";
}
if(where.Length > 0) {
    sql += "WHERE " + where;
}

Then you have to do the same to add the parameters to your command:

if (textBoxGUID.Text.Length > 0) command.Parameters.AddWithValue("@itemguid", textBoxGUID.Text);
if (textBoxBatchID.Text.Length > 0) command.Parameters.AddWithValue("@batchid", textBoxBatchID.Text);
if (textBoxAccount.Text.Length > 0) command.Parameters.AddWithValue("@account", textBoxAccount.Text);

It is a lot more code, but it allows you to search for exactly the parameters that are supplied.

Though it seems like a lot of messy/choppy code, it works perfectly! I'd hoped there would be a simpler, cleaner way of implementing this. (I liked @Goat CO's answer but it just didn't work for me). — Robula, Jun 30 '14 at 15:51
Thank you for this, it did the trick! I said messy because I believed there would be a nice short SQL string that would work, but perhaps I was wrong. I'll mark this as answer if nobody can give me said SQL string :) — Robula, Jun 30 '14 at 16:15

score 2 · Accepted Answer · answered Jun 30 '14 at 15:33

2

If I follow correctly, you want to filter results based on all populated variables, to handle unpopulated variables you need to add an OR for when the variable is empty (NULL):

WHERE (basket.itemGuid = @itemguid OR @itemguid IS NULL)
  AND (basket.batchid = @batchid OR @batchid IS NULL) 
  AND (basket.account = @account OR @account IS NULL)

answered Jun 30 '14 at 15:33

Hart CO

34,064
6
48
63

Thanks for the reply. None of the columns should ever be NULL. The user should be able to search by GUID or BatchID or Account or a combination of any of those. I'll try this and see if it works. :) – Robula Jun 30 '14 at 15:41
Can't return any data using this. Because the Parameters are populated from a TextBox.Text, wouldn't `@itemguid`, `@batchid` and `@account` be String.Empty as opposed to NULL? – Robula Jun 30 '14 at 15:47
@Robula Yes, you'd have to change `NULL` to whatever the variable is when not populated. – Hart CO Jun 30 '14 at 15:57
Using `WHERE (basket.itemGuid = @itemguid OR @itemguid = '') AND (basket.batchid = @batchid OR @batchid = '') AND (basket.account = @account OR @account = ''"` returns a SQL error: "Incorrect syntax near ''". – Robula Jun 30 '14 at 16:11
1

@Robula `WHERE (basket.itemGuid = @itemguid OR @itemguid = '') AND (basket.batchid = @batchid OR @batchid = '') AND (basket.account = @account OR @account = '')` ? – Hart CO Jun 30 '14 at 16:30
Ooops! My mistake! This is working, thank you! Marking this as answer for the cleanest, most elegant solution. – Robula Jul 01 '14 at 07:33
The problem with this is that SQL Server doesn't usually re-optimize for differing parameters, so you end up with non-optimal plans. – Clockwork-Muse Jul 01 '14 at 11:25

score 1 · Answer 3 · edited Apr 26 '18 at 17:29

1

Another way,

@"SELECT TOP 10 primaryFile 
FROM dbo.basket 
WHERE 1 = case when ISNULL(@itemguid, basket.itemguid) = basket.itemGuid then 1 else 0 end
and   1 = case when ISNULL(@batchid, basket.batchid) = basket.batchid then 1 else 0 end
and   1 = case when ISNULL(@account, basket.account) = basket.account then 1 else 0 end"

edited Apr 26 '18 at 17:29

rollstuhlfahrer

3,988
9
25
38

answered Jun 30 '14 at 16:51

sentha selvaratnam

51
2

score 1 · Answer 4 · answered Jul 01 '14 at 06:19

1

You can use this:

string sql = @"SELECT TOP 10 primaryFile FROM dbo.basket WHERE 1 = 1";

if (textBoxGUID.Text.Length > 0) 
{
    sql += " AND basket.itemGuid = @itemguid";
}
if (textBoxBatchID.Text.Length > 0) 
{
    sql += " AND basket.batchid = @batchid";
}
if (textBoxAccount.Text.Length > 0) 
{
    sql += " AND basket.account = @account";
}

answered Jul 01 '14 at 06:19

Jesuraja

3,774
4
24
48

`WHERE 1 = 1` provides a shorthand adding additional criteria, I like it. – Robula Jul 01 '14 at 07:29

Multiple optional WHERE Parameters

4 Answers4

Linked

Related