Posted by & filed under Web Development.

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;

Leave a Reply

  • (will not be published)