A very powerful yet obscure feature in SQL is the ability to have parts of the where clause conditionally execute.

Most often developers will make duplicate copies of an entire query to facilitate two different variable choices.

Below is an example stored procedure to return employees that has an optional parameter to return employees based on GenderType. If no values is passed into the parameter, all employees will be returned. If the parameter is filled it will return the employees of that gendertype.

Many developers will write the query this way:

CREATE PROCEDURE spGetEmployees
(
	@GenderType varchar(20) = NULL
)
AS
BEGIN
IF @GenderType IS NOT NULL
	BEGIN
		SELECT *
		FROM dbo.Employees e
		WHERE e.GenderType = @GenderType
	END
ELSE
	BEGIN
		SELECT *
		FROM dbo.Employees e
	END
END

As you can see, the same query is repeated twice. This accounts for twice the maintenance and two different execution plans being generated (depending on which version of SQL Server you are using).

Now we’ll write the same query with a conditional where clause

CREATE PROCEDURE spGetEmployees
(
	@GenderType varchar(20) = NULL
)
AS
BEGIN

	SELECT *
	FROM dbo.Employees e
	WHERE (@GenderType IS NULL OR e.GenderType = @GenderType)

END

What we did here is utilize the power of the OR statement in the Where clause. In an or statement if the left side of the OR statement evaluates to true, then the second side is not evaluated. So when no value is entered for @GenderType, then the left side of the OR is true, so the right side is never evaluated.

This has the same effect as using an if statement in the first example without the overhead of maintenance.

This solution will also typically only generate one execution plan which can be a pro or a con in some situations. It is usually a con if the current query is overly complex. In these cases it can sometimes cause confusion for the query optimizer.

Using the power of the or statement, you can also nest multiple conditions.