Queries with optional parameters

SQL Server Best Practices
SQL Server Best Practices
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

Continue reading on SQLStudies.com.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *