检查列表是否为空或空，然后检查SQL。

Question

我有这样的疑问，

with t as (
      <your query here>
     )
select t.id, t.name, t.randomid, trand.name as randomname
from t left join
     t trand
     on t.randomid = trand.id
where t.id in (select item from dbo.ufnSplit(@ids,','));

Here is code for ufnSplit

如何添加检查，以便如果@ids为空或空，然后使用where条件，只有在@ids不为空或空时，才需要此条件？

where t.id in (select item from dbo.ufnSplit(@ids,','));

Answer 1

这将检查@ if是空还是空，然后检查where子句。

with t as (
      <your query here>
     )
select t.id, t.name, t.randomid, trand.name as randomname
from t left join
     t trand
     on t.randomid = trand.id
where @ids = '' 
   or @ids is null
   or t.id in (select item from dbo.ufnSplit(@ids,','))

Answer 2

试试这个Where子句

where t.id in (select item from dbo.ufnSplit(@ids,',')) or nullif(@ids,'') is null;

Answer 3

您只需使用一个条件WHERE子句，如下所示：

with t as (
      <your query here>
     )
select t.id, t.name, t.randomid, trand.name as randomname
from t left join
     t trand
     on t.randomid = trand.id
where @ids IS NULL OR t.id IN (select item from dbo.ufnSplit(@ids,','));

因此，如果它是NULL，它将返回所有内容，否则它将计算WHERE子句的OR部分。

您可能需要编辑函数：dbo.ufnSplit来优雅地处理NULL输入，这样才能正常工作。