[SOLVED] Conditionally filter results of query based on a single flag

Conditionally filter results of query based on a single flag

I have a simple table with two fields -- an ID field (Primary Key, INT), and a PartNumber field (VARCHAR). What I'm trying to do is conditionally filter down to the part numbers that begin with either "C" or "E" if a specific expression is set to 1, otherwise show all rows. Is this possible? I've provided code below to better demonstrate my question:

/* Set up sample table */
DROP TABLE IF EXISTS #TestTbl

CREATE TABLE #TestTbl (ID INT PRIMARY KEY CLUSTERED,
                       PartNumber VARCHAR(100))


INSERT INTO #TestTbl (ID,
                      PartNumber)
SELECT t.ID,
       t.PartNumber
FROM   (VALUES (1, 'A11111'),
               (2, 'A12341'),
               (3, 'B12232'),
               (4, 'C11111'),
               (5, 'E99999'),
               (6, 'A10101'),
               (7, 'L99999'),
               (8, 'T49390')) t(ID, PartNumber)
                
/* Make sure I can query it...and this also represents the results I want if the expression is not set to 1 */
SELECT t.*
FROM   #TestTbl t

/* This query represents the effect of what I would like if the expression is set to 1 */
SELECT t.*
FROM   #TestTbl t
WHERE  t.PartNumber LIKE 'C%'
       OR t.PartNumber LIKE 'E%'

/* This is my attempt to limit to PartNumbers that start with "C" or "E" in a way that requires me to change only one value.
   This doesn't work. Instead 1 returns all rows and 0 returns no rows.*/
SELECT *
FROM   #TestTbl t
       CROSS APPLY (SELECT FilterFlag = IIF(t.PartNumber LIKE 'C%' OR t.PartNumber LIKE 'E%', 1, NULL)) ca
WHERE  (CASE
          WHEN ca.FilterFlag IS NOT NULL AND ca.FilterFlag = 1 THEN 1
          WHEN ca.FilterFlag IS NULL THEN 1
          ELSE 0
        END) = 1 /* I would like to be able to toggle this "= 1" between 1 and 0.
                    1 would show only PartNumbers that start with "C" or "E" and 0 wouldn't apply the filter at all and therefore show all rows */

There may be another way to approach this but ideally this would be done in a single query and there would only be one value I would have to change to apply/not apply the filter (preferably at the end).

Thanks in advance!

Solution

This can be solved by creating two sets of data, where one set is all and other is the filtered one.

SELECT *
FROM   #TestTbl t
CROSS APPLY (
        SELECT  t.*, 0 AS FilterFlag
        UNION ALL
        SELECT t.*, 1
        WHERE   t.PartNumber LIKE 'C%' OR t.PartNumber LIKE 'E%'
    ) x
WHERE  x.FilterFlag = 1 -- or 0

This works but looks a bit "weird" in my eyes. Usually, for such filters, it's more easier to just do:

SELECT *
FROM   #TestTbl t
WHERE  (t.PartNumber LIKE 'C%' OR t.PartNumber LIKE 'E%') OR @someParameter IS NULL

Since you often use a parameter to create your filter condition.