Change SQL where clause based on a parameter?

Question

I need to alter a query to do something like this (following is generic pseudo-code):

if (tag list contains all tags in the database) {
    select every product regardless of tag, even products with null tag
}
else { //tag list is only a few tags long
    select only the products that have a tag in the tag list
}

I have tried doing stuff like this, but it doesn't work:

SELECT p.Id
FROM Tags t
    JOIN Products p ON p.TagId = t.Id
WHERE ((EXISTS(select Id from Tags EXCEPT select item from dbo.SplitString(@tagList,',')) AND p.TagId in (select item from dbo.SplitString(@tagList,',')))
    OR (p.TagId in (select item from dbo.SplitString(@tagList,',')) or p.TagId is null))

This will take place inside of a large query with a large WHERE clause, so putting two slightly different queries in an IF ELSE statement is not ideal.

What should I do to get this working?

Can you execute an anonymous block instead of single statement? — T.S.
– T.S., Commented Nov 22, 2021 at 23:45
Do you have a list of all tags in the database? Is there such a table? — Charlieface
– Charlieface, Commented Nov 23, 2021 at 0:28
How exactly do you expect to populate @tagList? And why do you use a scalar variable rather than a table? Would it not be inefficient to "fill" a scalar variable with all possible tag values in order to "select everything"? Typically we use special "flag" values (usually NULL) to allow the query to ignore a filter and include everything. Suggest you give Erland's entire website a look but esp. the discussion of dynamic search conditions — SMor
– SMor, Commented Nov 23, 2021 at 0:39
@Charlieface there is a Tag table, and a product can have a single TagId, which is a FK to the Tag table. so, select * from Tags would list all of the Tags in the database. — RyanB
– RyanB, Commented Nov 23, 2021 at 3:45

Charlieface · Accepted Answer · 2021-11-23 09:21:04Z

First things first: you should use properly normalized input parameters. Ideally this would be a Table-Valued parameter, however if you cannot do that then you could insert the split values into a table variable

DECLARE @tags TABLE (TagId int PRIMARY KEY);
INSERT @tags (TagId)
SELECT item
FROM dbo.SplitString(@tagList, ',');

Next, the easiest way is probably to just find out first whether all tags match, and store that in a variable.

DECLARE @isAllTags bit = CASE WHEN EXISTS(
    SELECT t.Id
    FROM Tags t
    EXCEPT
    SELECT tList.Id
    FROM @tags tList
) THEN 0 ELSE 1 END;

SELECT p.Id
FROM Products p
WHERE @isAllTags = 1
  OR EXISTS (SELECT 1
      FROM @tags tList
      WHERE tList.TagId = p.TagId);

You could merge these queries, but it's unlikely to be more performant.

You could even do it in a very set-based fashion, but it's probably going to be really slow

SELECT p.Id
FROM Products p
WHERE EXISTS (SELECT 1
  FROM Tags t
  LEFT JOIN @tags tList ON tList.TagId = t.Id
  CROSS APPLY (VALUES (CASE WHEN p.TagId = tList.TagId THEN 1 END )) v(ProductMatch)
  HAVING COUNT(t.Id) = COUNT(tList.TagId)  -- all exist
      OR COUNT(v.ProductMatch) > 0              -- at least one match
);

T.S. · Accepted Answer · 2021-11-23 00:22:29Z

0

Try this, this might work.

SELECT p.Id
FROM 
    Products p LEFT JOIN
    Tags t  ON p.TagId = t.Id
WHERE 
    t.Id is null
    OR
    (t.id is not null and 
        t.Id in (SELECT value FROM STRING_SPLIT(@tagList, ',')))

I just tested - works

edited Nov 23, 2021 at 0:22

answered Nov 22, 2021 at 23:55

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

2 Comments

RyanB Over a year ago

In this case, t.Id is never null. I gave this a try and it did not work.

T.S. Over a year ago

@RyanB left join guarantees that when there are no matching records, it will be null. Your try only suggests that in your database each left record has a matching record. I created fiddle in my DB on existing data (I changed column names) and it worked

Collectives™ on Stack Overflow

Change SQL where clause based on a parameter?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related