I ran into some nasty SQL today while updating a report at work. The report accepted several parameters that were optional. When a parameters value was set to ‘ALL’ then the filter was ignored. To handle the optional parameters the previous developer had created a set of WHERE statements for each scenario that the parameters could exist as. Here is the WHERE clause in question. Each scenario is separated by ‘OR ( BP.BS_Paid = 1 )’. Good thing there were only three optional parameters.
The nasty WHERE clause: (bear with me on the names in the SQL. The snippet is from a health care application.)
WHERE ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( BP.BS_Performed_Date BETWEEN @ProcedurePerformed_From
AND @ProcedurePerformed_To )
AND ( SUBSTRING(PS.Site_num, 2, 2) = REPLICATE('0', 2 - LEN(@LCC))
+ @LCC )
AND ( PS2.Site_num = @Provider )
OR ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( SUBSTRING(PS.Site_num, 2, 2) = REPLICATE('0', 2 - LEN(@LCC))
+ @LCC )
AND ( PS2.Site_num = @Provider )
AND ( @ProcedurePerformed_ALL = 'ALL' )
OR ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( BP.BS_Performed_Date BETWEEN @ProcedurePerformed_From
AND @ProcedurePerformed_To )
AND ( PS2.Site_num = @Provider )
AND ( @LCC = 'ALL' )
OR ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( PS2.Site_num = @Provider )
AND ( @ProcedurePerformed_ALL = 'ALL' )
AND ( @LCC = 'ALL' )
OR ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( BP.BS_Performed_Date BETWEEN @ProcedurePerformed_From
AND @ProcedurePerformed_To )
AND ( SUBSTRING(PS.Site_num, 2, 2) = REPLICATE('0', 2 - LEN(@LCC))
+ @LCC )
AND ( @Provider = 'ALL' )
OR ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( SUBSTRING(PS.Site_num, 2, 2) = REPLICATE('0', 2 - LEN(@LCC))
+ @LCC )
AND ( @ProcedurePerformed_ALL = 'ALL' )
AND ( @Provider = 'ALL' )
OR ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( BP.BS_Performed_Date BETWEEN @ProcedurePerformed_From
AND @ProcedurePerformed_To )
AND ( @LCC = 'ALL' )
AND ( @Provider = 'ALL' )
OR ( BP.BS_Paid = 1 )
AND ( FTProc.Description = 'BreastProcedureName' )
AND ( @ProcedurePerformed_ALL = 'ALL' )
AND ( @LCC = 'ALL' )
AND ( @Provider = 'ALL' )
This mess begged to be refactored, so I sketched out my problem on the white board in the office and got some input from the team. We came up with three techniques for handling optional parameters.
Technique #1. WHERE clause if..then
This technique essentially uses the an OR operator to decide if the parameter is missing. When the first expression evaluates to true then the second expression is ignored making the parameter optional.
Using this technique the WHERE clause can be refactored to the following.
WHERE BP.BS_Paid = 1
AND FProc.TypeID = 13
AND (@Provider = 'ALL' OR PS2.Site_num = @Provider)
AND (@ProcedurePerformed_ALL = 'ALL'
OR (BP.BS_Performed_Date > @ProcedurePerformed_From
AND BP.BS_Performed_Date < @ProcedurePerformed_To))
AND (@LCC = 'ALL'
OR SUBSTRING(PS.Site_num, 2, 2) = REPLICATE('0', 2 - LEN(@LCC))+ @LCC ))
The WHERE clause is much more maintainable in this version. In addition, the filter can use complex expressions, such as, date ranges.
Technique #2. ISNULL or COALESCE operators
This technique requires that the parameter be NULL when ignoring optional parameters. The COALESCE operator will use the first NON NULL value. When a parameter is NULL then the COALESCE uses the column’s value as the second expression, there by setting the column equal to its self.
This technique works fine when evaluating expressions with ‘=’ operator. However, using the ‘<>’ operators don’t work when comparing a column to itself. I was forced to comment out the date range expressions are a result of this limitation.
Here is the results from using this technique.
WHERE BP.BS_Paid = 1
AND FProc.TypeID = 13
AND dbo.GetLCCFromSite(PS.Site_num) = COALESCE(@LCC, dbo.GetLCCFromSite(PS.Site_num))
-- AND (BP.BS_Performed_Date > COALESCE(@ProcedurePerformed_ALL,@ProcedurePerformed_From)
-- AND BP.BS_Performed_Date < COALESCE(@ProcedurePerformed_ALL,@ProcedurePerformed_To))
AND PS2.Site_num = COALESCE(@Provider, PS2.Site_num)
Technique #3. Dynamic query using sp_executesql
This technique builds a SQL query on the fly and then executes it using sp_executesql. Dynamically building the SQL allows for WHERE expressions to be omitted all together from the query. The IF….ELSE construct is used to determine if optional parameters should be included in the query. I am speculating that this is the best performing technique, because parameters are not even included in the query.
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
SET @SQLString = N'WHERE BP.BS_Paid = 1 AND FProc.TypeID = 13';
IF @Provider != 'ALL'
BEGIN
SET @SQLString = @SQLString + N' AND PS2.Site_num = @xProvider';
END
IF @ProcedurePerformed_ALL != 'ALL'
BEGIN
SET @SQLString = @SQLString + N' AND (BP.BS_Performed_Date > @xProcedurePerformed_From AND BP.BS_Performed_Date < @xProcedurePerformed_To))';
END
IF @ProcedurePerformed_ALL != 'ALL'
BEGIN
SET @SQLString = @SQLString + N' AND SUBSTRING(PS.Site_num, 2, 2) = REPLICATE(''0'', 2 - LEN(@xLCC))+ @xLCC ))';
END
SET @ParmDefinition = N'@@xProvider NVARCHAR(10)
,@xProcedurePerformed_From DATETIME
,@xProcedurePerformed_To DATETIME
,@xLCC INT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@Provider
,@ProcedurePerformed_From
,@ProcedurePerformed_To
,@LCC;
Web Development
SQL
