SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Queries with optional parameters

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.


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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...