Hello all,
how can I make a WHERE clause parametrized?
For example, I need to write a stored that select some fields
based on a list of parameters, and not all of them are provided
by the user.
If I have all the parameters, the SELECT should be:
SELECT *
FROM dbo.Customers
WHERE City = @City
AND State = @State
AND Department = @Department
AND OldCustomer = @OldCustomers
If the user pass @OldCustomers = -1
the SELECT should be:
SELECT *
FROM dbo.Customers
WHERE City = @City
AND State = @State
AND Department = @Department
and so on.
And if the user pass everything -1,
the SELECT should be:
SELECT *
FROM dbo.Customers
What is it the best solution to write a stored like this?
This type of queries are called "catch-all queries" or "dynamic search conditions". The quick and intuitive way of doing this type of query would be to write the query like shown below.
SELECT col1, col2, col3
FROM dbo.Customers
WHERE
(City = @City OR @City = '-1')
AND ([State] = @State OR @State = '-1')
AND (Department = @Department OR @Department = '-1');
Usually people pass NULL value rather than -1, but that is really a matter of preference. Having a default value of NULL and omitting the parameter is what I have seen usually done.
The problem with this approach is that it can generate really really poor query plans for most combinations of input parameters. This is due to what they call "parameter sniffing" The way to get around is to use dynamic SQL. Look through these articles to see how to do that safely: