Conditional Where Clause

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: [cc lang=”sql”] 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 [/cc] 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 [cc lang=”sql”] CREATE PROCEDURE spGetEmployees ( @GenderType varchar(20) = NULL ) AS BEGIN SELECT * FROM dbo.Employees e WHERE (@GenderType IS NULL OR e.GenderType = @GenderType) END [/cc] 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 […]