Can I know the issue of this SQL query-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

The function dbo.split will split the string in more than one value. This will confuse your subquery and the error you are receiving will be thrown.

In case you need what goes before the comma consider using:

select top 1 value 
from dbo.split(@Facility, ','))

CodePudding user response：

You want to say if the variable is NULL so ignore the WHERE statement, if so your query would be:

SELECT *
FROM Facility f
WHERE @Facility IN (select value from dbo.split(@Facility, ',')) OR @Facility IS NULL