Multi-valued Parameter Query issue

Question

Post reply

Multi-valued Parameter Query issue

Jabba1963

SSC-Addicted

Points: 402
More actions
February 22, 2016 at 8:35 am

#308235

I have a parameter query dataset returning CallType, CallTypeName, CallType + " - " + CallTypeName as Prompt
CallType - char(1) - alpha - about 15 values eg. A, W, T, R, P, etc...
I have set up my parameter in SSRS as data type Text multivalued
With Prompt as the Label and CallType as Value returned
My main dataset query works fine when I select one value or pass null in SSMS but fails if I select multiple values of CallType.
ie. I select W - all fine - records are returned and all contain a CallType of W
I select T - all fine - records are returned and all contain a CallType of T
I select A - all fine - records are returned and all contain a CallType of A
In SSMS - I select WTA - expecting all the above records and query runs but returns no rows
In SSRS / SSDT Query Builder it has similar results...
And when I run the report directly I get an error message...
"An expression of non-Boolean type specified in context where a condition is expected, near ','."
The pseudo query looks like this...
Select whatever from wherever
where FieldOnTableCallType IN (@prmCallType) OR @prmCallType IS NULL
Scouring around the internet it appears that @prmCallType when multi-values are selected does not always work as expected within your query and so I want to split the @prmCallType string into several strings and then do the IN... so that... "AWT" entered as the parameter to represent CallTypes A and W and T translates to a string to represents "A", "W", "T" and then hopefully the IN coding will work...
Anyone got a function to split a string "AWT" and return 'A','W','T' as a string so that my WHERE statement will then work ?
Alternatively if anyone knows the resolution to the underlying query for multi-valued parameters then great...
Help much appreciated 🙂

Viewing 12 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Google: DelimitedSplit8k

Use the function in the article

btw, it's working exactly as expected in all cases (but maybe not how you hoped though)

FieldOnTableCallType IN (@prmCallType) is equivalent to FieldOnTableCallType = @prmCallType

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jabba1963 SSC-Addicted Points: 402 More actions · Answer 2

Thanks Gail - love the Qui-Gon Jinn icon 🙂

Looked at the article and slightly confused as to how to utilise the function... no I lie - correction - I am very confused since I am not delimiting a string

I do however appreciate the subtlety of...

FieldOnTableCallType IN (@prmCallType) is equivalent to FieldOnTableCallType = @prmCallType

Hence I guess I am looking for

FieldOnTableCallType IN ('A','W','T') where @prmCallType = "'A','W','T'"

So that I can then execute

FieldOnTableCallType IN (@prmCallType)

Grateful for further pointers...

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

It's a table valued function, so join to it. Instead of WHERE IN, join to the function on the column that you're filtering by.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Jabba1963 (2/22/2016)
Looked at the article and slightly confused as to how to utilise the function... no I lie - correction - I am very confused since I am not delimiting a string

I'm not sure what you call delimiting a string. You have values in a string delimited by commas. The function will allow you to split those values into independent strings.

Take your time with the article, it's certainly something that people can't get in 20 minutes. Don't think about wasted time for this issue, think of it as an investment of time you'll save for future occasions.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jabba1963 SSC-Addicted Points: 402 More actions · Answer 5

Hi Luis,

I guess why I am confused is that I am not trying to split a string based on a delimiter - I am trying to build a string with a delimiter

ie. I have "WAT" I want "'W','A','T'"

But yes point taken I will persevere with the article - although it seems very performance focused whereas I am not too concerned about performance in this case.

And thanks for responding - much appreciated

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

I am very confused since I am not delimiting a string

If you are using multi-select in SSRS then SSRS is passing a comma-delimited string to your query or stored proc. For example, if you select the values A, B and C then the string that SSRS is sending to SQL Server is 'A,B,C'.

The function delimitedsplit8K (that Gail referenced) can be used to "split" those values into rows like so:

Query

DECLARE @prmCallType VARCHAR(8000) = 'A,B,C';

SELECT ItemNumber, Item

FROM dbo.DelimitedSplit8K_AB(@prmCallType,',');

Results

ItemNumber Item

------------ ---------

1 A

2 B

3 C

To accomplish what you are trying to do you would replace this:

Select whatever

from wherever

where FieldOnTableCallType IN (@prmCallType)

OR @prmCallType IS NULL

With this:

SELECT whatever

FROM wherever

JOIN dbo.delimitedsplit8K(@prmCallType,',')

ON FieldOnTableCallType = item

OR @prmCallType IS NULL

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jabba1963 SSC-Addicted Points: 402 More actions · Answer 7

Thanks Alan

Beautifully and succinctly put... I now also understand yours and Gail's reference to it being a table valued function

I have now successfully added function... and have executed the simply select as per your example... and all is good...

Struggled with the JOIN for a bit but then figured out where the item element was coming from <hits forehead with palm of hand!!>

Still alludes me that this is not handled when a report is rendered... and it seems a number of articles on setting up multi-valued parameters utilise and refer to using the IN statement directly as I did initially. I won't post links here in case it goes against forum rules.

But thankfully I now know what to do if and when I get this issue again.

Thanks again much appreciated 🙂

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

Glad to help.

<hits forehead with palm of hand!!>

Me too. So much so that I now have a receding hairline :hehe:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sergiy SSC Guru Points: 110209 More actions · Answer 9

For non-delimited string you don't need a splitter, use just a Tally table:

DECLARE @prmCallType VARCHAR(8000) = 'ABC';

SELECT N, SUBSTRING(@prmCallType, N,1)

FROM dbo.TallyGenerator(1, LEN(@prmCallType), 1) tg

_____________
Code for TallyGenerator

Jabba1963 SSC-Addicted Points: 402 More actions · Answer 10

Hi Sergiy

Thanks for this... a whole new arena to get to grips with.... Tally Tables...

I did a little reading up last night and this approach will prove most useful

Thanks

Jabba1963 SSC-Addicted Points: 402 More actions · Answer 11

To anyone interested...

Underlying cause for the my original WHERE {field on table} IN @{Multi-value Parameter} issue not working as usual

Symptoms - Query working in SSMS and SSDT Query Builder but the SSRS Report itself failing to render and run in SSDT causing "An expression of non-Boolean type specified in context where a condition is expected, near ','." error.

Resolution - the standard approach of the WHERE clause DOES NOT WORK in my version of SSDT for SQL Server 2012 - but DOES WORK if I use Report Builder 3.0 OR Visual Studio 2012 directly !! Does not figure but wasted enough time on this to delve further right now.

The good news is that I have learnt more about table valued functions AND indeed the Tally Table methods along the way - all good stuff

Thanks to all