Queries with optional parameters
20February 27, 2017 by Kenneth Fisher
These are those queries where you are pulling for, let’s say, a first name, a last name, a state, and/or a city. Simple enough, until you notice that or. We might only get a first name, or a state and the query still needs to work. These queries are commonly called
- Catch all queries
- Kitchen sink queries
- Swiss army knife queries
And a bunch of things that aren’t really printable here.
Typically you see these queries in stored procedures but for the sake of convenience, I’m going to just use variables and ignore the difference between variables and parameters.
Here are a few frequent patterns you’ll see
Single query
SELECT * FROM [Person].[vAdditionalContactInfo] WHERE (@FirstName IS NULL OR FirstName = @FirstName) AND (@LastName IS NULL OR LastName = @LastName) AND (@City IS NULL OR City = @City) AND (@StateProvince IS NULL OR StateProvince = @StateProvince)
Pro: Maintenance is easy. You only have a single query and it’s easy to add a new parameter or make changes to the query as needed.
Con: Performance stinks. This format is not SARGABLE. So even if you have an index on LastName (for example) it won’t be used for that particular clause.
Multiple queries
DECLARE @FirstName nvarchar(50) , @LastName nvarchar(50) , @City nvarchar(50) , @StateProvince nvarchar(50); IF @FirstName IS NOT NULL IF @LastName IS NOT NULL IF @City IS NOT NULL IF @StateProvince IS NOT NULL SELECT * FROM [Person].[vAdditionalContactInfo] WHERE FirstName = @FirstName AND LastName = @LastName AND City = @City AND StateProvince = @StateProvince; ELSE SELECT * FROM [Person].[vAdditionalContactInfo] WHERE FirstName = @FirstName AND LastName = @LastName AND City = @City; ELSE SELECT * FROM [Person].[vAdditionalContactInfo] WHERE FirstName = @FirstName AND LastName = @LastName; --- And so on, and so on, and so on
Pro: We get a separate query plan for each combination of parameters so performance is great. (Well, as good as can be anyway.)
Con: Maintance stinks. We need 16 different queries when we have 4 parameters and the numbers increase dramatically as we add additional parameters. So any change we make to the base query will have to be changed 16 times, and/or adding a new parameter means careful logic and adding a bunch of new queries.
Dynamic SQL
DECLARE @FirstName nvarchar(50) , @LastName nvarchar(50) , @City nvarchar(50) , @StateProvince nvarchar(50); DECLARE @sql nvarchar(max); SET @sql = 'SELECT * FROM [Person].[vAdditionalContactInfo] WHERE 1=1 '; IF @FirstName IS NOT NULL SET @sql = @sql + ' AND FirstName = @FirstName'; IF @LastName IS NOT NULL SET @sql = @sql + ' AND LastName = @LastName'; IF @City IS NOT NULL SET @sql = @sql + ' AND City = @City'; IF @StateProvince IS NOT NULL SET @sql = @sql + ' AND StateProvince = @StateProvince'; EXEC sp_executesql @sql, N'@FirstName nvarchar(50) , @LastName nvarchar(50) , @City nvarchar(50) , @StateProvince nvarchar(50)' , @FirstName, @LastName, @City, @StateProvince;
Pro: Even though we don’t have to physically write a separate query for each combination of parameters we still get a separate query plan for each one. Not to mention that they are SARGABLE so indexes can be used. Because of all this we get the best possible performance for each query. Maintenance isn’t so bad either. Adding another parameter isn’t trivial but it isn’t overly difficult either.
Con: Lots of people are afraid of dynamic SQL. It isn’t nearly as simple as writing a regular query, although with some practice it isn’t all that hard.
In general, for this type of query, the best way to handle it is the dynamic method. If you need some help with dynamic queries in general, I’ve got a couple of helpful posts here:
Writing Dynamic SQL (A how to)
Best practice recommendations for writing Dynamic SQL
I’ll admit I was a bit lazy here and didn’t do the performance proofs I could have for the three options I’ve described. But aside from the fact that I like shorter blogs, I also think people learn better when they try things for themselves. Consider this homework. Next time you need a query like this try the single query and the dynamic query and do some performance tests of your own.
SQL Studies 
Thanks for good article. I think, the best article about dynamic SQL: http://sommarskog.se/dyn-search.html
*nod* It’s certainly one of the best I’ve seen.
Aren’t you opening up yourself up to SQL injection attacks with the dynamic query? Especially in this case where you appear to be taking human input?
Not in this case. I’m using parameterized dynamic SQL. In other words I’m creating the dynamic SQL to still use variables, then passing those variables in. Where you run into problems with SQL injection is pieces of code like this
SET @SQL = @SQL + ‘ AND City = ”’ + @City + ”’;
In that case I’m constructing the dynamic SQL to have a literal string and then execute it. That opens us to a risk of @City = ”’test”; drop table blah;’ or something similar.
I’ve been using a pattern like:
SELECT *
FROM [Person].[vAdditionalContactInfo]
WHERE FirstName = ISNULL(@FirstName, FirstName)
AND LastName = ISNULL(@LastName, LastName)
AND City = ISNULL(@City, City)
AND StateProvince = ISNULL(@StateProvince, StateProvince);
In all my tests it is faster than your first query, but probably still not sargable and not nearly as fast as the parameterized dynamic SQL. Thanks for the great tip.
Great example! I’ve seen that one around a lot too. And you are right, it isn’t sargable and won’t be nearly as fast as the dynamic version. Thanks for the comment!
As long as `City` is never NULL
Is there an easy way to print (debug) what is being executed in sp_executesql, where it shows the query with the values, and not with the parameters?
Not to my knowledge. No more than you can get the parameters for a stored procedure. If you think about it it’s really a security thing. The parameters being used could give you information you aren’t supposed to have.
Thanks for the article, I’ve been trying to learn dynamic sql but I need examples so that it’s easier to understand and grasp the concepts.
Yea, Dynamic SQL can be very difficult at first. A good starting point is writing scripts that return code for you to run. For example
[…] Continue reading on SQLStudies.com. […]
Hi! I forgot to validate a null parameter, but the sp works ok. i tried to test it on a normal query but didn’t work. Is a new option? Auto validate optional parameter
I don’t remember when it became an option but you can do this:
Note how I set the parameter = to something when I created it. Now that parameter is optional. Or am I misunderstanding your question?
I usually do this
WHERE Field = isnull(@Val, Field ) / WHERE (Field = @Val or @Valor is null)
but i forget do that and i just wrote
WHERE Field = @Val
and the sp works anyway
Ahh .. I’m talking about a stored procedure running without having to pass in a parameter. If you don’t give it a default value then the parameter is required and the call will fail without it.
Which now that I look at the post and your original comment has absolutely nothing to do with what you were saying.
Starting again: You might check the setting of ANSI_NULLs. It can change the behavior of how NULLs are treated. It’s still safer to handle them separately just in case though.
Another way I’ve used sometimes:
IF @FirstName IS NULL
SET @FirstName = ‘%’
If @LastName IS NULL
SET @LastName = ‘%’
IF @City IS NULL
SET @City = ‘%’
IF @StateProvince IS NULL
SET @StateProvince = ‘%’
SELECT *
FROM [Person].[vAdditionalContactInfo]
WHERE (FirstName LIKE @FirstName)
AND (LastName LIKE @LastName)
AND (City LIKE @City)
AND (StateProvince LIKE @StateProvince)
What if you want to capture partial strings? How do you account for partial strings and optional parameters? For example, the full last name is “Smith” but I want to show “Smith” if the user types, “Smi”.
That’s going to be a coding issue. The parameter is “Smi” regardless. If you want to return “Smith” then you would add a % at the end of the parameter and use a LIKE as part of your query.
If you mean you want an autofill, that’s on the application where you are entering the parameter.
Does that answer your question?