SQL for including rows based on list in input variable or all rows if variable is null

Question

I have an varchar input variable that contains a comma delimited list of integers that are in one of the columns in my select statement. I know how to split the list and use this in a where clause for example:

DECLARE @ListOfAges Varchar
SET @ListOfAges = '15,20,25'

select p.Name, a.Age 
from People p
left join Ages a on p.AgeKey = a.AgeKey
Where a.Age in (dbo.Split(@ListOfAges))

What I'd like to do is if the @ListOfAges var is null, to select everything, so something like this:

select p.Name, a.Age 
from People p
left join Ages a on p.AgeKey = a.AgeKey
Where (@ListOfAges = null OR a.Age in (dbo.Split(@ListOfAges)))

Is this there a way to do this that performs better? Possibly without using the IN clause or not in the WHERE clause? I wasn't sure if including this in the join would be possible, or if an entirely different approach is recommended (such as not using comma separated input variables).

Thanks!

How's the performance when you replace in (dbo.Split(@ListOfAges)) with in (15,20,25)? Is it substantially better? This is the upper bound for the performance that you can achieve by eliminating the split and doing nothing else. If this performance is not acceptable, you may want to see if there's other stuff to optimize there. — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Jan 10, 2014 at 16:30

Sparky · Accepted Answer · 2014-01-10 16:33:08Z

1

The function call in the WHERE clause is slowing you down. This approach is not dramatically better, but should help a bit..

WHERE (@ListOfAges is NULL OR
       CHARINDEX(','+TRIM(STR(A.AGE))+',',','+@ListOfAges+',')) > 0)

Again, not an ideal approach, but should perform better than the function call.

I would try the following to optimize performance..

Create a temporary table with the list of ages.
LEFT JOIN this table with Ages on a.Age using alias TT
WHERE CLAUSE should be

WHERE @ListOfAges is null OR NOT isNull(TT.Age)

answered Jan 10, 2014 at 16:33

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

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

Comments

xspydr · Accepted Answer · 2014-01-10 16:32:57Z

0

You could use a case statement within the where clause if this is Sql 2005 or above...

http://msdn.microsoft.com/en-us/library/ms181765.aspx

answered Jan 10, 2014 at 16:32

xspydr

3,0703 gold badges32 silver badges49 bronze badges

Comments

Anon · Accepted Answer · 2014-01-10 16:39:25Z

0

select p.Name, a.Age 
from People p
left join Ages a on p.AgeKey = a.AgeKey
inner join dbo.Split(@ListOfAges) s on a.Age = s.Age
UNION ALL
select p.Name, a.Age 
from People p
left join Ages a on p.AgeKey = a.AgeKey
WHERE @ListOfAges IS NULL

answered Jan 10, 2014 at 16:39

Anon

10.9k1 gold badge32 silver badges31 bronze badges

Comments

JB9 · Accepted Answer · 2014-01-10 16:43:00Z

0

DECLARE @ListOfAges Varchar
SET @ListOfAges = '15,20,25'


if @ListOfAges in ('15,20,25')



 select p.Name, a.Age 
 from People p
 left join Ages a on p.AgeKey = a.AgeKey
Where a.Age in (dbo.Split(@ListOfAges))


 else

 select p.Name, a.Age 
 from People p   
 left join Ages a on p.AgeKey = a.AgeKey

answered Jan 10, 2014 at 16:43

JB9

498 bronze badges

Collectives™ on Stack Overflow

SQL for including rows based on list in input variable or all rows if variable is null

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related