Can I know the issue of this SQL query

I have this SQL query:

but I’m getting an error:

If I remove the comma-separated value from the variable, it is working fine. As well as if I remove the NULL checking feature it is working fine. Can I know the issue of this

>Solution :

It’s because a CASE WHEN can only return 1 value.
And a STRING_SPLIT returns a resultset.

I assume something like this is what you want.

SELECT *
FROM Facility f
WHERE (@Facility IS NULL OR f.facilityCode IN (SELECT value FROM string_split(@Facility,','))) 

This will get all records if the variable is null.