Queries with optional parameters


February 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
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 @StateProvince IS NOT NULL
				SELECT * 
				FROM [Person].[vAdditionalContactInfo]
				WHERE FirstName = @FirstName
				  AND LastName = @LastName
				  AND City = @City
				  AND StateProvince = @StateProvince;
				SELECT * 
				FROM [Person].[vAdditionalContactInfo]
				WHERE FirstName = @FirstName
				  AND LastName = @LastName
				  AND City = @City;
			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 ';

	SET @sql = @sql + '
		AND FirstName = @FirstName';
	SET @sql = @sql + '
		AND LastName = @LastName';
	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.

20 thoughts on “Queries with optional parameters

  1. Konstantin Taranov says:

    Thanks for good article. I think, the best article about dynamic SQL: http://sommarskog.se/dyn-search.html

  2. Bernie Basel says:

    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.

  3. Don A. says:

    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.

  4. Decrepit says:

    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.

  5. Jacque says:

    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

      SELECT 'PRINT '''+name+'''' FROM sys.databases
  6. lestatab says:

    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:

      CREATE PROCEDURE #test (@id int = 0) 
      PRINT @id
      EXEC #test

      Note how I set the parameter = to something when I created it. Now that parameter is optional. Or am I misunderstanding your question?

      • lestatab says:

        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.

  7. Shiffy says:

    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)

  8. tina says:

    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?

Leave a Reply to Tawani Anyangwe (@_tawani) Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: