Using IN in a Where statement with and option of all

Question

Using IN in a Where statement with and option of all

Ken at work

Hall of Fame

Points: 3426
More actions
February 2, 2017 at 7:15 am

#340108

I'm trying to use a
Where TS.Skill IN (@TS)
and it works fine. 🙂
The user can select one or any number of options from a drop down.
But now I'm trying something like this.
And (TS.Skill In (@TS) or @TS = '')
And it is failing. 🙁
I've also tried
And 1 = Case When @TS = 'All' Then 1 When @TS <> 'All' And TS.Skill In (@TS) Then 1 Else 0 End
I'd like the user to be able to select all, or any number of individual ones. I cannot use the 'Select All' from the drop down because the parameters are feed from another program and the drop down is not available. The best way, I thought, was to have one of the drop down options to be 'All'. If that is entered it act the same as if 'Select All' was chosen. However I can't seem to get it to work.
Any help would be appreciated.

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
@Field IS NULL

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Ken at work - Thursday, February 2, 2017 7:15 AM
I'm trying to use a
Where TS.Skill IN (@TS)
and it works fine. 🙂
The user can select one or any number of options from a drop down.
But now I'm trying something like this.
And (TS.Skill In (@TS) or @TS = '')
And it is failing. 🙁
I've also tried
And 1 = Case When @TS = 'All' Then 1 When @TS <> 'All' And TS.Skill In (@TS) Then 1 Else 0 End
I'd like the user to be able to select all, or any number of individual ones. I cannot use the 'Select All' from the drop down because the parameters are feed from another program and the drop down is not available. The best way, I thought, was to have one of the drop down options to be 'All'. If that is entered it act the same as if 'Select All' was chosen. However I can't seem to get it to work.
Any help would be appreciated.

IN (@Variable) doesn't usually work because the column isn't compared to comma-separated elements in the variable, it's compared to the whole variable. There's no "macro substitution". Here's a very simple mockup of this:

IF OBJECT_ID('tempdb..#Clients') IS NOT NULL DROP TABLE #Clients; CREATE TABLE #Clients (Surname VARCHAR(30), Skill VARCHAR(2));

INSERT INTO #Clients (Surname, Skill) VALUES

('Smith', 'AB'), ('Smith', 'AB'), ('Smith', 'AB'), ('Smith', 'AB'),

('Jones', 'CD'), ('Jones', 'CD'), ('Jones', 'CD'), ('Jones', 'CD'), ('Jones', 'CD'),

('Sahathevarajan', 'EF'), ('Sahathevarajan', 'EF');

SELECT Surname, Skill FROM #Clients;

DECLARE @TS VARCHAR(100) = 'AB,CD,EF'

SELECT Surname, Skill FROM #Clients TS WHERE TS.Skill IN (@TS)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ken at work Hall of Fame Points: 3426 More actions · Answer 3

Thom A - Thursday, February 2, 2017 7:28 AM
Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
@Field IS NULL

For compatibility reasons I'm not testing for either '' or Null. Specifically I'm trying to get @TS = 'All' to work.
Also testing for Where @TS = 'All' or or testing for Where IN both work.

Just not together.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Ken at work - Thursday, February 2, 2017 7:49 AM
Thom A - Thursday, February 2, 2017 7:28 AM
Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
@Field IS NULL
For compatibility reasons I'm not testing for either '' or Null. Specifically I'm trying to get @TS = 'All' to work.
Also testing for Where @TS = 'All' or or testing for Where IN both work.
Just not together.

What code are you using for this, Ken? Looking at the execution plan from the little test ^^, you see this in the search predicate:

SELECT Surname, Skill FROM #Clients TS WHERE TS.Skill IN (@TS)

-- Predicate: [tempdb].[dbo].[#Clients].[Skill] as [TS].[Skill]=[@TS]

SELECT Surname, Skill FROM #Clients TS WHERE TS.Skill IN ('AB','CD')

-- Predicate: [tempdb].[dbo].[#Clients].[Skill] as [TS].[Skill]='AB' OR [tempdb].[dbo].[#Clients].[Skill] as [TS].[Skill]='CD'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Thom A SSC Guru Points: 99133 More actions · Answer 5

Ken at work - Thursday, February 2, 2017 7:49 AM
Thom A - Thursday, February 2, 2017 7:28 AM
Does @TS have a value of '', or does it not have a value (NULL)? To test for an empty value (variable or field), you need to use
@Field IS NULL
For compatibility reasons I'm not testing for either '' or Null. Specifically I'm trying to get @TS = 'All' to work.
Also testing for Where @TS = 'All' or or testing for Where IN both work.
Just not together.

I'm very surprised that selecting multiple values with TS.Skill IN (@TS) works. As Chris pointed out, SQL doesn't convert comma delimited strings into a range of values to match.

Personally, if you are using a comma delimited string, I would expect to use the DelimitedSplit8K such as:
WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ',')) OR @TS = 'All')

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ken at work Hall of Fame Points: 3426 More actions · Answer 6

I don't believe it is a comma delimited string; I'm still relatively new with SSRS. The program, if run on a web page from the SSRS server, will use the standard SSRS drop down. But when launched from our ERP the @TS can only be a single value. So I'm trying to get both to work.

If I use:
Where HRMst.ActiveYN = 'Y' And HRMst.Co = @Company And (TS.Skill In (@TS) or @TS = 'All')
When @TS = 'All' - it works.
When @TS = and single selection - it works
When @TS = multiple selections from the drop down in SSRS it fails.

Ken at work Hall of Fame Points: 3426 More actions · Answer 7

P.S. If I use
Where HRMst.ActiveYN = 'Y' And HRMst.Co = @Company And TS.Skill In (@TS)

It works with multiple selections, but that doesn't give me the ability to enter/select "All"; which isn't the same thing as clicking the 'Select All' from the drop down.

Thom A SSC Guru Points: 99133 More actions · Answer 8

Considering that you are using the IN clause, it is HEAVILY implied that you are providing multiple values, as that's what the IN clause is for. if you are only supplying one value, use the = operator.

For multiple values, see my post about Delimited8KSplit.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99133 More actions · Answer 9

Let me elaborate a bit more. We've not had a lot of information on your SSRS report set up, so I'm running on guesswork here however.

I assume you are getting a list of your values you can select from @TS on your SSRS report from a dataset. Maybe something like:
SELECT DISTINCT Skill FROM MyTable;
This is only going to provide you with a list of valid Skills, which I would imagine won't have a skill called "All". You'll need to therefore use a query like:
SELECT DISTINCT Skill FROM MyTable UNION SELECT 'All' AS Skill;
This gives you a skill "All" in your selection box.

THEN you can use the WHERE statement i provided above:
WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ',')) OR @TS = 'All')

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ken at work Hall of Fame Points: 3426 More actions · Answer 10

Thom A - Thursday, February 2, 2017 8:28 AM
THEN you can use the WHERE statement i provided above:
WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ',')) OR @TS = 'All')

I'm not sure what SELECT Item FROM dbo.DelimitedSplit8K is and I can't get it to run for me.

Thom A SSC Guru Points: 99133 More actions · Answer 11

Ken at work - Thursday, February 2, 2017 8:42 AM
Thom A - Thursday, February 2, 2017 8:28 AM
THEN you can use the WHERE statement i provided above:
WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ',')) OR @TS = 'All')
I'm not sure what SELECT Item FROM dbo.DelimitedSplit8K is and I can't get it to run for me.

Thom A - Thursday, February 2, 2017 7:58 AM
I'm very surprised that selecting multiple values with TS.Skill IN (@TS) works. As Chris pointed out, SQL doesn't convert comma delimited strings into a range of values to match.
Personally, if you are using a comma delimited string, I would expect to use the DelimitedSplit8K such as:
WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ',')) OR @TS = 'All')

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Y.B. SSChampion Points: 11649 More actions · Answer 12

Ken at work - Thursday, February 2, 2017 8:42 AM
Thom A - Thursday, February 2, 2017 8:28 AM
THEN you can use the WHERE statement i provided above:
WHERE (TS.Skill IN (SELECT Item FROM dbo.DelimitedSplit8K(@TS, ',')) OR @TS = 'All')
I'm not sure what SELECT Item FROM dbo.DelimitedSplit8K is and I can't get it to run for me.

It's a function you would have to add as it is not part of SQL. Check out this article for more details as well as the code itself.
http://www.sqlservercentral.com/articles/Tally+Table/72993/

SELECT quote FROM brain WHERE original = 1
0 rows returned

Ken at work Hall of Fame Points: 3426 More actions · Answer 13

TS.Skill IN (@TS) works perfectly. I don't know if the drop down list of the SSRS parameter creates a a comma delimited string. I didn't think that was what was happening.

But I did find an alternate, though not perfect, solution.

You are correct this is what I'm been using from the start:
SELECT DISTINCT Skill FROM MyTable UNION SELECT 'All' AS Skill;

So the first thing I did was to eliminate the 'UNION SELECT 'All' AS Skill' part. Then in the SSRS report, Parameter Properties I selected the query as the default Value. Doing this causes the report, when run, to have all of the selections checked. Finally I dropped that parameter from being passed from our ERP. Since it isn't passed to beging with the default will always be used.

Basically if lauched from our ERP then it will have the effect of chosing 'Select All'. When run from a web page the user will see the drop down and be able to limit the list. Of course my preference would have been to allow the user the option of selecting all or a a single skill when using the ERP. Not the preferred solution but definitely workable.

Jay McGrath SSC-Addicted Points: 456 More actions · Answer 14

Try using
And (TS.Skill In (@TS) or NULLIF(@TS, '') IS NULL)
It gets around the NULL value issue.

Jay