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 NULLOR FirstName = @FirstName)
  AND (@LastName IS NULLOR LastName = @LastName)
  AND (@City IS NULLOR 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.

Filed under: Dynamic SQL, Microsoft SQL Server, Performance, SQLServerPedia Syndication, T-SQL Tagged: dynamic sql, microsoft sql server, Performance, T-SQL