Performing queries using optional parameters without Dynamic SQL

Imagine you have a application that will be querying your database. The user is presented 3 different fields that will be used to filter the results that are shown to the user. They are all optional fields. The question is how can I best implement the solution.

There are several ways to approach the problem.

Option 1: Dynamic SQL

While this is an option, it is prone to SQL injection and can be difficult and error prone trying to defend against it. I know you can use parameters with it to some extent, but I think it is still not a good choice for the following reasons. It can also be difficult to read because of all the extra quotes, formatting, etc. Debugging is also difficult at best. For these reasons, try to stay away from this option.

Option 2: LINQ

You can use LINQ to SQL or LINQ to Entities to solve the problem. I highly recommend LINQ for this purpose if you have the opportunity.

Below is a snippet of code that queries the Person table by 3 optional parameters. Be sure to create and close your context object. This does not use a stored procedure, but LINQ is by design protected from SQL Injection. This is an extremely easy way to implement the desired functionality.

var people = from p in ctx.Person
            select p;
                           
if (nameParam != null)
{
    people = people.Where(p => p.Name == nameParam);
}

if (phoneParam != null)
{
    people = people.Where(p => p.Phone == phoneParam);
}

if (ssnParam != null)
{
    people = people.Where(p => p.SSN == ssnParam);
}

return people;

Option 3: Stored Procedure

Perhaps you don’t have LINQ or know LINQ yet. I recommend you take this as an opportunity and learn it, but that is a personal choice. If you can’t use LINQ or perhaps the project you are doesn’t use it and you want to keep the way of accessing the database consistent within the project, then LINQ may not be a good choice.

Within a stored procedure you could write if else statements to control what queries are executed. However, this can get messy for more than two optional parameters. This is because you need to have 4 cases and 4 select statements. If you have three optional parameters, then you have 8 cases and 8 select statements. If you have four optional parameter, then you have 16 cases and 16 select statements. As you can see this option is not a very scalable solution, and I don’t recommend it.

I do however recommend the following technique which uses OR and NULL checks in the where clause. Below is an example of a T-SQL snippet that does the same thing as the LINQ example.

select * from Person
where 
((@Name IS NULL) OR (Name = @Name))
AND
((@Phone IS NULL) OR (Phone = @Phone))
AND
((@SSN IS NULL) OR (SSN = @SSN))

Here is the same example, but with begins with search.

select * from Person
where 
((@Name IS NULL) OR (Name = @Name))
AND
((@Phone IS NULL) OR (Phone like @Phone + '%'))
AND
((@SSN IS NULL) OR (SSN like @SSN + '%'))

You can also use Coalesce or isnull. However, It becomes complex to try to do like instead of equal to. I didn’t actually complete this in my test due to the complexity and desire for simplicity of using the above example.

For information on performance of Coalesce, ISNULL, and NULL / OR combination, click here.