How to verify if array list is empty in SQL

Question

I have a multilist field returning a list of values.

My query is filtering the list using IN (list), but if the user do not select anything is list, it returns an empty list, or null (I can't see).

I'm trying to do something like:

    case when @list is null then 1
             when c.SK_dimLocalizacao in (@list) then 1
             else 0
        end = 1

It works when I select none or one value, but if I select more than one it bring me an error.

I already tried to use:

    case when @list = '' then 1
             when c.SK_dimLocalizacao in (@list}) then 1
             else 0
        end = 1

I can't modify the query in backend because i'm using Pentaho.

one suggestion, you can use Tools > sql server profiler to see what is executing as @list in sql server management studio. — Adnan Sharif
– Adnan Sharif, Commented Aug 19, 2019 at 18:57
Please show @list declaration. If it's a string type, it will not work this way. You'll need to pass a user-defined table type and join — Serg
– Serg, Commented Aug 19, 2019 at 19:17

Aaron Dietz · Accepted Answer · 2019-08-19 19:15:49Z

2

Maybe this? (not sure if I understand the end goal)

IF LEN(ISNULL(@list,'')) <> 0
BEGIN
   --Query for if the list isn't empty or null
END
ELSE
   --Query for if the list is empty or null

answered Aug 19, 2019 at 19:15

Aaron Dietz

10.3k1 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1