Conditional Where Clause
-
Posted on August 14, 2009 by Derek Dieter
-
0
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 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.
The most efficient way to write this however (which sort of falls outside the scope of this article however we can’t ignore) would be to use a union. Sometimes OR can cause a scan on the underlying tables because the qualifying result set is too large. This may or may not be an option for you.
[cc lang=”sql”]
CREATE PROCEDURE spGetEmployees
(
@GenderType varchar(20) = NULL
)
AS
BEGIN
SELECT *
FROM dbo.Employees e
WHERE @GenderType IS NULL
UNION
SELECT *
FROM dbo.Employees e
WHERE e.GenderType = @GenderType
END
[/cc]
- Comments (RSS)
- Trackback
- Permalink
Thank you very much, I was commissioned to optimize a WHERE with a CASE or (with the power of an) OR, but for a huge table it didn’t work as expected. The UNION example just does it.
In my case, a Top 1 helped make a difference.
Select id From
(( Select Top 1 id, a, b, c From t1 Where (id = @id))
Union
( Select id, a, b, c From t1 Where (@id Is Null))) As T1
…
Great article! Just what I was looking for! Thanks for sharing your knowledge.
Wow Greate tutorial…. Thanx
Thank You!!
I stumbled accross this today and it helped fix up my code perfectly.
Dereck…Nevermind. I figured out what needed to be done. Thanks anyway!
-Harvey
Why it keeps gettign cut off…I don’t know. But here is another attempt at pasting it in;
CASE WHEN DATEPART(mm, GetDate()) = 1 then
(DatePart(dd, pl.Plcmt_From_Dt) >= 11
and DatePart(mm, pl.Plcmt_From_Dt) = 12
and DatePart(Year, pl.Plcmt_From_Dt) = DatePart(Year, GetDate())-1
OR
DatePart(dd, pl.Plcmt_From_Dt) = 11 AND (DATEPART(mm, pl.Plcmt_From_Dt) = DATEPART(mm, GETDATE())-1
and DATEPART(Year, pl.Plcmt_From_Dt) = DATEPART(Year, GETDATE()))
OR
DATEPART(dd, pl.Plcmt_From_Dt) <= 10 AND DATEPART(mm, pl.Plcmt_From_Dt) = DATEPART(mm, GETDATE()) AND DATEPART(Year, pl.Plcmt_From_Dt) = DATEPART(Year, GETDATE())
end
Dereck,
I just subscribed to your newsletter and commented after using one of your posts to use the correct syntax for CAST functions. I have a question and am hoping that you can help me. I am writing a query where I am attempting to use a CASE expression in the WHERE clause using DATEPART. I am trying to build it so that if the month that a report is being run is January then to return records that are from the 11th of December of last year through the 10th of January of the current year ELSE return the records for the same date range of the current year. The code that I have is as follows;
CASE WHEN DATEPART(mm, GetDate()) = 1 then
(DatePart(dd, pl.Plcmt_From_Dt) >= 11
and DatePart(mm, pl.Plcmt_From_Dt) = 12
and DatePart(Year, pl.Plcmt_From_Dt) = DatePart(Year, GetDate())-1
OR
DatePart(dd, pl.Plcmt_From_Dt) = 11 AND (DATEPART(mm, pl.Plcmt_From_Dt) = DATEPART(mm, GETDATE())-1
and DATEPART(Year, pl.Plcmt_From_Dt) = DATEPART(Year, GETDATE()))
OR
DATEPART(dd, pl.Plcmt_From_Dt) ‘.
Any help you can offer would be greatly appreciated!
Sincerely,
Harvey
Thank you so much for this posting. I used it in a query at work and it fit the bill 100%. Saved me a bunch of work
