From time to time, I have a need to utilize a conditional WHERE clause. In other words, I need to create a stored procedure that filters for or against different parameters, depending upon varying criteria. In the past, I tried creating stored procedures using an IF statement to select one query over another. I’ve also used dynamic T-SQL (yes, ‘tis true), again, with an IF statement. The problem is that both solutions really aren’t. Solutions, that is. (NOTE: Neither is this - due to some flaws in testing, this appeared to be a valid solution. It isn't. It works for smaller tables but it isn't scalable. Go ahead and play with it, if you will, but please do not use it in production. JH)
Now I believe that I have a solution, one which is sargable. We know that the SQL Optimizer discards tautologies immediately, so a “1=1” in the WHERE clause spends about a nanosecond of CPU time for it for the entire query. But what if we use a “1=@MyCondition”? Since the @MyCondition is a variable, the Optimizer doesn’t recognize the statement as a tautology. This allows us the opportunity to use any number of different sargable WHERE conditions, depending on our need.
Before we “take a deep dive” into this, allow me to explain the sproc, a bit. The basic design was put to me as a challenge, not long ago, to create a list of university school weeks by WeekNumber and DayOfTheWeekNumber, i.e., Monday of the first week of school would be W1D1, Tuesday would be W1D2, etc. This was to be done using a Common Table Expression (CTE). This is probably the only way to perform this (here’s another challenge for you: can you do this WITHOUT using a CTE?).
I took the challenge a bit further by extending the date range beyond what a recursive CTE allows (100 iterations), using a table variable, and by showing/not showing holidays (the holidays selected are not necessarily represented as the actual holidays, but merely as examples) as such. Here is my CTE code:
if @MidDate<@pSchoolEndDate begin ;with cte ([Date],Wk,DOW) as ( select @StartDate,@Wk,datepart(dw,@StartDate) union all select [Date]+1,case when DOW=1 then Wk+1 else Wk end,datepart(dw,[Date]+1) from cte where [Date]<@MidDate ) insert into @Dates select 'W'+cast(Wk as varchar)+'D'+cast(DOW-1 as varchar) ,convert(varchar(10),[Date],101) ,Wk ,DOW from cte where 1=1 and datepart(dw,[Date]) not in (1,7) end
I chose a MERGE JOIN to handle the holidays; whether to show them, or not.
merge @Dates as d using (select [Date] from @Holidays) h on d.[Date]=h.[Date] when matched and 0=@pShowHolidays then delete when matched and 1=@pShowHolidays then update set d.WeekAndDay='Holiday' when not matched then insert([Date]) values('19000101');
Since this sproc has us pass in the holidays, we need to use Jeff Moden’s Tally Table (or Integer table) to convert the Comma-Delimited list of holidays. We check the number of actual characters passed into the sproc for holidays to determine how many iterations are needed with @CharCount with this code:
declare @CrLf char(2),@Delim char(1),@CharCount int declare @Holidays table([Date] datetime) declare @integers table(n int identity(1,1) not null primary key clustered,x char(1)) select @Delim=',' set @pHolidayList=@Delim+@pHolidayList+@Delim set @pHolidayList=replace(@pHolidayList,@Delim+@Delim,@Delim) set @CharCount=len(@pHolidayList) set rowcount @CharCount insert into @integers select 'x' from dbo.syscolumns sc1 cross join dbo.syscolumns sc2 set rowcount 0 insert into @Holidays select cast(substring(@pHolidayList,n+1,charindex(@Delim,@pHolidayList,n+1)-(n+1)) as datetime) from @integers where n<len(@pHolidayList) and substring(@pHolidayList,n,1)=@Delim
We also need to populate The Days Of The Week , so, since they are static, we can just insert them directly into a table variable.
insert into @Days select 1,'Sun' union all select 2,'Mon' union all select 3,'Tue' union all select 4,'Wed' union all select 5,'Thu' union all select 6,'Fri' union all select 7,'Sat'
Now let speculate, not wildly, that the university schedules classes by the following criteria:
College Algebra: Mon-Fri
Calculus: Mon-Wed-Fri
Linear Algebra: Tue, Thu
Differential Equations: Mon-Wed-Fri
Advanced Calculus (Elementary Topology): Mon, Wed
Modern Algebra: Wed
The condition [array] uses the following:
where (0=@pUseCondition or (1=@pUseCondition and d1.[WeekDay]='mon') or (2=@pUseCondition and d1.[WeekDay]='tue') or (3=@pUseCondition and d1.[WeekDay]='wed') or (4=@pUseCondition and d1.[WeekDay]='thu') or (5=@pUseCondition and d1.[WeekDay]='fri') or (6=@pUseCondition and d1.[WeekDay] in ('mon','wed','fri')) or (7=@pUseCondition and d1.[WeekDay] in ('tue','thu')) or (8=@pUseCondition and d.[Date]>=@pEarliestDate) or (9=@pUseCondition and d1.[WeekDay] in ('mon','fri')) or (10=@pUseCondition and patindex(@HideDay,d.WeekAndDay)=0)
This is the flexibility of this technique; you may set whatever conditional CONDITIONS you require. Here is the sproc:
use TempDB go if exists (select * from dbo.sysobjects where id=object_id(N'dbo.tp_TestConditional_WHERE_Clause') and objectproperty(id,N'IsProcedure')=1 ) drop procedure dbo.tp_TestConditional_WHERE_Clause go set ansi_nulls on go set quoted_identifier on go /*-------------------------------------------------------------------------------------- Procedure: tp_TestConditional_WHERE_Clause Called by: declare @pSchoolStartDate datetime,@pSchoolEndDate datetime,@pHolidayList varchar(max),@pUseCondition int ,@pShowHolidays bit,@pHideDay int,@pEarliestDate datetime exec TempDB.dbo.tp_TestConditional_WHERE_Clause @pSchoolStartDate='20120826' ,@pSchoolEndDate='20130531' ,@pHolidayList='20121129,20121130,20121225,20130101,20130107' ,@pUseCondition=6 -- 0=No conditions; 1=Mon,2=Tue,3=Wed,4=Thu,5=Fri; 6=Mon,Wed,Fri; 7=Tue,Thu; 8=Earliest date to return; 9=Mon,Fri; 10=Exclude @pHideDay ,@pShowHolidays=1 -- 0=Hide Hols, 1=Show Hols ,@pHideDay=0 -- Use DOW number (1=Mon,2=Tue,3=Wed,4=Thu,5=Fri) Use with @pUseCondition=10 ,@pEarliestDate='20130228' History: 20130316, jhick Created procedure. --------------------------------------------------------------------------------------*/ create procedure [dbo].[tp_TestConditional_WHERE_Clause] ( @pSchoolStartDate datetime ,@pSchoolEndDate datetime ,@pHolidayList varchar(max)='19000101' ,@pUseCondition int=0 ,@pShowHolidays bit=1 ,@pHideDay int=0 ,@pEarliestDate datetime=0 ) as begin set nocount on; --####################################################### Main Code ####################################################### declare @CrLf char(2),@Delim char(1),@CharCount int declare @Holidays table([Date] datetime) declare @integers table(n int identity(1,1) not null primary key clustered,x char(1)) select @Delim=',' set @pHolidayList=@Delim+@pHolidayList+@Delim set @pHolidayList=replace(@pHolidayList,@Delim+@Delim,@Delim) set @CharCount=len(@pHolidayList) set rowcount @CharCount insert into @integers select 'x' from dbo.syscolumns sc1 cross join dbo.syscolumns sc2 set rowcount 0 insert into @Holidays select cast(substring(@pHolidayList,n+1,charindex(@Delim,@pHolidayList,n+1)-(n+1)) as datetime) from @integers where n<len(@pHolidayList) and substring(@pHolidayList,n,1)=@Delim ----======================================================================================================================= declare @StartDate datetime,@MidDate datetime,@Wk int,@HideDay char(3) declare @Dates table(WeekAndDay varchar(10) null,[Date] datetime null,Wk int null,DOW int) declare @Days table(DOW int,[WeekDay] char(3)) set @HideDay='%D'+cast(@pHideDay as varchar) insert into @Days select 1,'Sun' union all select 2,'Mon' union all select 3,'Tue' union all select 4,'Wed' union all select 5,'Thu' union all select 6,'Fri' union all select 7,'Sat' select @StartDate=@pSchoolStartDate,@MidDate=dateadd(dd,100,@StartDate) select @Wk=case when datepart(dw,@StartDate)!=2 then 0 else 1 end if @MidDate<@pSchoolEndDate begin ;with cte ([Date],Wk,DOW) as ( select @StartDate,@Wk,datepart(dw,@StartDate) union all select [Date]+1,case when DOW=1 then Wk+1 else Wk end,datepart(dw,[Date]+1) from cte where [Date]<@MidDate ) insert into @Dates select 'W'+cast(Wk as varchar)+'D'+cast(DOW-1 as varchar) ,convert(varchar(10),[Date],101) ,Wk ,DOW from cte where 1=1 and datepart(dw,[Date]) not in (1,7) end select @StartDate=max([Date]),@Wk=max(@Wk) from @Dates select @Wk=Wk from @Dates where [Date]=@StartDate set @StartDate=dateadd(dd,1,@StartDate) set @MidDate=dateadd(dd,100,@StartDate) if @MidDate<@pSchoolEndDate begin ;with cte ([Date],Wk,DOW) as ( select @StartDate,@Wk,datepart(dw,@StartDate) union all select [Date]+1,case when DOW=1 then Wk+1 else Wk end,datepart(dw,[Date]+1) from cte where [Date]<@MidDate ) insert into @Dates select 'W'+cast(Wk as varchar)+'D'+cast(DOW-1 as varchar) ,convert(varchar(10),[Date],101) ,Wk ,DOW from cte where 1=1 and datepart(dw,[Date]) not in (1,7) end select @StartDate=max([Date]),@Wk=max(@Wk) from @Dates select @Wk=Wk from @Dates where [Date]=@StartDate set @StartDate=dateadd(dd,1,@StartDate) set @MidDate=dateadd(dd,100,@StartDate) if @MidDate<@pSchoolEndDate begin ;with cte ([Date],Wk,DOW) as ( select @StartDate,@Wk,datepart(dw,@StartDate) union all select [Date]+1,case when DOW=1 then Wk+1 else Wk end,datepart(dw,[Date]+1) from cte where [Date]<@MidDate ) insert into @Dates select 'W'+cast(Wk as varchar)+'D'+cast(DOW-1 as varchar) ,convert(varchar(10),[Date],101) ,Wk ,DOW from cte where 1=1 and datepart(dw,[Date]) not in (1,7) end select @StartDate=max([Date]),@Wk=max(Wk) from @Dates if @StartDate is null begin set @StartDate=@pSchoolStartDate set @Wk=1 end else begin select @Wk=Wk from @Dates where [Date]=@StartDate end set @StartDate=dateadd(dd,1,@StartDate) set @MidDate=dateadd(dd,100,@StartDate) ;with cte ([Date],Wk,DOW) as ( select @StartDate,@Wk,datepart(dw,@StartDate) union all select [Date]+1,case when DOW=1 then Wk+1 else Wk end,datepart(dw,[Date]+1) from cte where [Date]<@pSchoolEndDate ) insert into @Dates select 'W'+cast(Wk as varchar)+'D'+cast(DOW-1 as varchar) ,convert(varchar(10),[Date],101) ,Wk ,DOW from cte where 1=1 and datepart(dw,[Date]) not in (1,7) ----############################################## merge @Dates as d using (select [Date] from @Holidays) h on d.[Date]=h.[Date] when matched and 0=@pShowHolidays then delete when matched and 1=@pShowHolidays then update set d.WeekAndDay='Holiday' when not matched then insert([Date]) values('19000101'); ----############################################## select d.WeekAndDay,d1.[WeekDay],convert(varchar(10),d.[Date],101) as [Date] from @Dates d join @Days d1 on d1.DOW=d.DOW where (0=@pUseCondition or (1=@pUseCondition and d1.[WeekDay]='mon') or (2=@pUseCondition and d1.[WeekDay]='tue') or (3=@pUseCondition and d1.[WeekDay]='wed') or (4=@pUseCondition and d1.[WeekDay]='thu') or (5=@pUseCondition and d1.[WeekDay]='fri') or (6=@pUseCondition and d1.[WeekDay] in ('mon','wed','fri')) or (7=@pUseCondition and d1.[WeekDay] in ('tue','thu')) or (8=@pUseCondition and d.[Date]>=@pEarliestDate) or (9=@pUseCondition and d1.[WeekDay] in ('mon','fri')) or (10=@pUseCondition and patindex(@HideDay,d.WeekAndDay)=0) ) ----======================================================================================================================== ----##################################################### End Main Code ##################################################### end ---- tp_TestConditional_WHERE_Clause go set quoted_identifier off go set ansi_nulls on go --grant execute on dbo.tp_TestConditional_WHERE_Clause to UserList --go print @@servername
Looking at the WHERE condition, we see this:
where (0=@pUseCondition or (1=@pUseCondition and d1.[WeekDay]='mon') or (2=@pUseCondition and d1.[WeekDay]='tue') or (3=@pUseCondition and d1.[WeekDay]='wed') or (4=@pUseCondition and d1.[WeekDay]='thu') or (5=@pUseCondition and d1.[WeekDay]='fri') or (6=@pUseCondition and d1.[WeekDay] in ('mon','wed','fri')) or (7=@pUseCondition and d1.[WeekDay] in ('tue','thu')) or (8=@pUseCondition and d.[Date]>=@pEarliestDate) or (9=@pUseCondition and d1.[WeekDay] in ('mon','fri')) or (10=@pUseCondition and patindex(@HideDay,d.WeekAndDay)=0)
The “0=@pCondition” tells our sproc to ignore all subsequent @pCondition filters. The “or (1=@pCondition=1...)” tells the query engine to focus on this filter, wherein we are seeking Mondays, only. The @pShowHolidays filter resides in the MERGE JOIN code, above, and is not affected by the conditional WHERE, except where the @pCondition forces or rejects certain days of the week.
Using the calling code embedded within the sproc (instructions for use are included as comments),
declare @pSchoolStartDate datetime , @pSchoolEndDate datetime , @pHolidayList varchar(max) , @pUseCondition int , @pShowHolidays bit , @pHideDay int , @pEarliestDate datetime exec TempDB.dbo.tp_TestConditional_WHERE_Clause @pSchoolStartDate='20120826' ,@pSchoolEndDate='20130531' ,@pHolidayList='20121129,20121130,20121225,20130101,20130107' ,@pUseCondition=0 -- 0=No conditions; 1=Mon,2=Tue,3=Wed,4=Thu,5=Fri; 6=Mon,Wed,Fri; 7=Tue,Thu; 8=Earliest date to return; 9=Mon,Fri; 10=Exclude @pHideDay ,@pShowHolidays=1 -- 0=Hide Hols, 1=Show Hols ,@pHideDay=0 -- Use DOW number (1=Mon,2=Tue,3=Wed,4=Thu,5=Fri) Use with @pUseCondition=10 ,@pEarliestDate='20130228'
Let’s test this out. If we change just the @pCondition, our results will differ according to the @pCondition that we choose. For example, if we call the sproc using @pUseCondition=0 and @pShowHolidays=1, we get everything back, including the holidays, marked as such. If, however, we call the sproc using @pUseCondition=1, we are returned only Mondays, no matter what else we selected.
Let’s take this a step further and set @pUseCondition=10. Let us also set @pHideDay=4. This should hide ALL Thursdays, holiday, or not.
I sincerely hope that this has been of some value to you. With this having been a problem for so long, I was skeptical about introducing any code utilizing such a technique into production, so I tested this into oblivion. If I am way off my rocker on this, please let me know in the discussion (but, back it up with facts, not vitriol). Also, please bear in mind that my company doesn’t allow me to participate in forums or blogs on company machines, so I will be able to respond only after hours in US Mountain Time. I genuinely hope that is actually a solution and not another problem.