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.
Popular search terms:
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