Condition on the WHERE clause

Question

My script has a variable that does not always contain a value. If there is a value, I need an extra WHERE clause.

DECLARE @identityUserIds NVARCHAR;
SELECT @identityUserIds = (SELECT TOP (1) [userIdsCsv] FROM [identityUsers])

SELECT *
FROM [Users]
-- IF (!ISNULL(@identityUserIds) OR @identityUserIds != '')
WHERE [ID] in (@identityUserIds)
-- ELSE (no extra condition)

How can I do that?

I suspect your `in` condition won't work how you want it to, because the `userIdsCsv` value is just a string with commas in it, not a set of numbers. You will need to split it up first, e.g. as described [here](http://stackoverflow.com/questions/3735152/most-succinct-way-to-transform-a-csv-string-to-an-table-in-tsql) — Rhumborl, May 20 '16 at 10:45
That is correct, but here is just a pseudo code to illustrate the actual problem. Thanks for the link! — Moslem Ben Dhaou, May 20 '16 at 10:59

score 1 · Answer 1 · answered May 20 '16 at 10:42

1

A common way to do this is:

 ... where @identityUserIds is null or ID in (@identityUserIds)

answered May 20 '16 at 10:42

Anton Gogolev

113,561
39
200
288

I'm not sure, but isn't that `@identityUserIds != ''` part important? – René Vogt May 20 '16 at 10:46
@RenéVogt Might be, depending on whether `userIdsCsv` allows and/or can contan empty strings – Anton Gogolev May 20 '16 at 10:47
I assumed that it does because it was part of OP's query. – René Vogt May 20 '16 at 10:48
`ID in (@identityUserIds)` will still be evaluated and will fail if `@identityUserIds = ''` – Moslem Ben Dhaou May 20 '16 at 10:57

score 0 · Answer 2 · edited May 20 '16 at 11:25

0

I think this can simply be done with an OR:

SELECT *
FROM [Users]
WHERE Len(ISNULL(@identityUserIds,''))=0
    OR [ID] IN (@identityUserIds)

But note that @Rhumborl's comment is correct, the IN statement will not work with a string. Unfortunatly I have no immediate solution for that problem.

edited May 20 '16 at 11:25

shadow

1,883
1
16
24

answered May 20 '16 at 10:43

René Vogt

43,056
14
77
99

score 0 · Answer 3 · answered May 20 '16 at 12:32

0

...where ID in (ISNULL(@identityUserIds,ID))

answered May 20 '16 at 12:32

Sanjay

342
2
9

Condition on the WHERE clause

3 Answers3