CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2019
Mike S
Mike S

Reputation: 13

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!

Upvotes: 0

Views: 133

Answers (3)

Florin
Florin

Reputation: 555

You can solve this using a nested IIF function in the where clause:

declare @flag int =1
;with your_data as (
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)
)select * from your_data
where iif(@flag=1 and (partnumber like 'c%' or partnumber like 'e%'), partnumber, iif(@flag <>1, partnumber, ''))=partnumber;

Upvotes: 0

siggemannen
siggemannen

Reputation: 9272

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.

Upvotes: 1

Dale K
Dale K

Reputation: 27388

You overcomplicated it - you don't need a CASE expression, just simple AND/OR logic.

Also returning a FilterFlag of null just added to the complexity.

DECLARE @ToggleFlag bit = 1;
  
SELECT *
FROM #TestTbl t
CROSS APPLY (SELECT FilterFlag = IIF(t.PartNumber LIKE 'C%' OR t.PartNumber LIKE 'E%', 1, 0)) ca
WHERE @ToggleFlag = 0 OR ca.FilterFlag = 1;

You don't even need the CROSS APPLY

SELECT *
FROM #TestTbl t
WHERE @ToggleFlag = 0 OR t.PartNumber LIKE 'C%' OR t.PartNumber LIKE 'E%';

Note: @ToggleFlag = 0 can be replaced by any expression you desire - it doesn't need to be a variable.

Upvotes: 2

Related Questions