Queries with optional parameters

10

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
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.

10 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.

    • 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!

  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

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 1,653 other followers

Follow me on Twitter

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