Check if list is empty or null then check SQL

Question

I have a query like this,

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

How can I add check so that if @ids is null or empty only then use where condition, I want this condition only if @ids is not null or empty ?

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

score 3 · Accepted Answer · answered Mar 15 '16 at 08:55

This will check if @ids is null or empty, and then check the where clause.

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,','))

Pரதீப் · Answer 2 · 2016-03-15T09:00:57.513

1

Try this Where clause

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

edited Mar 15 '16 at 09:00

answered Mar 15 '16 at 08:51

Pரதீப்

91,748
19
131
172

score 1 · Answer 3 · answered Mar 15 '16 at 08:51

You can just use a conditional WHERE clause like so:

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,','));

So if it's NULL it will return everything, otherwise it will evaluate the OR part of the WHERE clause.

You may need to edit the function: dbo.ufnSplit to handle NULL input gracefully for this to work.

Check if list is empty or null then check SQL

3 Answers3