How to optionally apply a WHERE IN clause based on a variable?

Question

I have a stored procedure that needs to filter by a list of ids that are passed as a comma-delimited list (ie '1,2,3').

I want to apply a WHERE IN clause that will match those ids but ONLY if the variable contains anything (IS NOT NULL AND <> '').

Here's a simplified fiddle of the problem: http://sqlfiddle.com/#!18/5f6be/1

It's currently working for single and multiple ids. But when passing '' or NULL it should return everything but it's not returning anything.

The CTEs and pagination stuff is there for a reason, please provide a solution that doesn't change that.

I strongly suggest getting rid of that `CSVToTable` function (that uses a `WHILE`) and use a dataset approach; such as an XML splitter or tally table splitter (like `delimitedsplit8K_(lead)`). — Thom A, Mar 08 '19 at 21:36
[Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Lukasz Szozda, Mar 08 '19 at 22:06
If you really want to learn everything about this, here is the quintessential answer: [Erland Sommarskog's Dynamic Search Conditions in T‑SQL](http://www.sommarskog.se/dyn-search-2008.html) — George Menoutis, Mar 08 '19 at 23:11

score 5 · Answer 1 · answered Mar 08 '19 at 21:41

5

Using DelimitedSplit8k_lead you could achive this by doing:

CREATE PROC YourProc @List varchar(8000) = NULL AS
BEGIN

    SELECT {YourColumns}
    FROM YourTable YT
         OUTER APPLY dbo.DelimitedSplit8k_Lead(@List,',') DS
    WHERE DS.item = YT.YourColumn
       OR NULLIF(@List,'') IS NULL
    OPTION (RECOMPILE);
END

The OUTER APPLY is used, as if NULL is passed the dataset won't be eliminated. the RECOMPILE is there, as it turns into a "Catch-all query" with the addition of handling the NULL.

answered Mar 08 '19 at 21:41

Thom A

88,727
11
45
75

I'm using SQL 2016 locally and SQL Azure. Can STRING_SPLIT be used instead of that custom UDF? – empz Mar 08 '19 at 21:49
Great. That worked (using STRING_SPLIT). Also, I didn't need the OPTION (RECOMPILE) to handle both '' and NULL cases. – empz Mar 08 '19 at 21:52
2

Yes, @emzero , which begs the question of why were you using a `WHILE` to split a string even more? A `WHILE` is an awfully slow method to achieve what you were after. – Thom A Mar 08 '19 at 21:53
1

@emzero don't remove the `RECOMPILE` option, if this is an SP. I put it there for good reason. Unless you want poor performance in the future? – Thom A Mar 08 '19 at 21:54
Wait, something it's not working. I'll get back to you. – empz Mar 08 '19 at 21:56
3

@emzero, the OPTION(RECOMPILE) is there to stop the query engine from caching a query plan that's optimized for one set of parameters (or no parameters at all), when the next execution will have a different number of parameters (or no parameters at all), which will result in a sub-optimal execution plan. Don't drop it. – Eric Brandt Mar 08 '19 at 21:56
The problem is that I'm already joining by that column I'm also filtering with the delimited ids. With your solution, the JOIN clause condition gets ditched and everything is returned when not passing any id. My example doesn't show that because it's hugely simplified. I'll try to replicate it. – empz Mar 08 '19 at 22:04
Nevermind. It was a problem with some extra parenthesis needed in the before the AND. – empz Mar 08 '19 at 22:15
FYI, if you want a `STRING_SPLIT` "polyfill" for older versions of SQL, you can use this [TVF](https://gist.github.com/ripfire44/ad11539224fb5b38090a4568f47de12b) – alans Mar 08 '19 at 22:26
@alans I link to a different resource in this post (as the OP had tagged SQL Server 2012 at the time). That link takes me to a multi-statement table-value function; I actually doubt it'll out perform the (great) work done by Jeff Model and Eirikur Eiriksson on their iTVF. They both, alongside the SSC community, spent a huge amount of time making the function incredibly efficient. Especially as the solution you've linked uses an RBAR rCTE. – Thom A Mar 08 '19 at 22:30

alans · Accepted Answer · 2019-03-08T22:29:37.213

3

Why not use JOIN instead of a subquery in the WHERE clause.

set @userIds = nullif(ltrim(@userIds),'')
select u.*
from Users u
left join string_split(@userIds,',') s on u.Id=s.value
where s.value is not null or @userIds is null

edited Mar 08 '19 at 22:29

answered Mar 08 '19 at 22:06

alans

1,022
9
17

I have exactly the same idea: https://stackoverflow.com/a/36980115/5070879 – Lukasz Szozda Mar 08 '19 at 22:07
Nice. It's much more easier to understand it this way. – empz Mar 08 '19 at 22:22

Jorge Candeias · Answer 3 · 2019-03-08T22:17:54.913

1

The old school method:

WHERE
(@userIds IS NULL OR @userIds = '' OR U.Id IN (SELECT * FROM STRING_SPLIT(@userIds, ',')))

Add OPTION (RECOMPILE) at the end for this to work and trim the plan.

Edit: Based on comments, this one generates two table scans. It didn't make a difference in my LocalDb setup but don't rely on it regardless.

WHERE U.Id IN
(
   SELECT * FROM STRING_SPLIT(@userIds, ',')
   UNION ALL
   SELECT U.Id WHERE NULLIF(@userIds, '') IS NULL
)

edited Mar 08 '19 at 22:17

answered Mar 08 '19 at 21:51

Jorge Candeias

670
6
12

3

Repeating the comment above, the OPTION(RECOMPILE) is there to stop the query engine from caching a query plan that's optimized for one set of parameters (or no parameters at all), when the next execution will have a different number of parameters (or no parameters at all), which will result in a sub-optimal execution plan. Don't drop it. – Eric Brandt Mar 08 '19 at 21:57
It is neccessary if you want to get better execution plans. Often times this type of query could/should be split into two separate procedures with a main procedure to call the correct one based on the parameters. That way each procedure can keep a cached execution plan that is well suited for the current result set. – Sean Lange Mar 08 '19 at 22:00
This method also results in 2 scans of the table, rather than 1, which could result in a performance hit. – Thom A Mar 08 '19 at 22:00

How to optionally apply a WHERE IN clause based on a variable?

3 Answers3