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)

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

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.