I'm using this query to get news articles by tags.
SELECT N.*
FROM News N
WHERE EXISTS (SELECT 1
FROM STRING_SPLIT(N.Tags, ',') s1 JOIN
STRING_SPLIT('tag1,tag2,tag3', ',') s2
ON s1.value = s2.value
);
I'm wondering if there is any possibility to return all news articles if no tags were defined in query. For example:
SELECT N.*
FROM News N
WHERE EXISTS (SELECT 1
FROM STRING_SPLIT(N.Tags, ',') s1 JOIN
STRING_SPLIT(NULL, ',') s2
ON s1.value = s2.value
);
If we assume that the value being passed to s2
is a parameter, as the above doesn't make a lot of sense otherwise, you could use an OR
. I also suggest adding OPTION (RECOMPILE)
as the query plan for the NULL
and non-NULL
queries will likely be different, thus this stops poor query caching:
SELECT N.* --Replace with Columns
FROM dbo.News N
WHERE EXISTS (SELECT 1
FROM STRING_SPLIT(N.Tags, ',') s1
JOIN STRING_SPLIT(@Param, ',') s2 ON s1.value = s2.value)
OR @Param IS NULL
OPTION (RECOMPILE);
You can read more on why OPTION (RECOMPILE)
is a good choice in Revisiting catch-all queries and An Updated Kitchen Sink Example.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments