Multivalue parameter with AllowNull = true is not working ...

[Wayde]

I have a multi-value parameter, @CountryID, that can also be set to null.  

I have a sql database query that uses this parameter like such:

"select * from Countries where CountryID is null or CountryID in (@CountryID)

Running the report, everything works as expected when I set the parameter value to NULL or to a single value.  However, if I set it equal to two or more values I get the following error:

An error has occurred while processing Table 'crosstab1': An expression of non-boolean type specified in a context where a condition is expected, near ','.

What am I missing?  How do I configure the parameter and/or adjust the query so that it works with NULL, single values and multiple values?

Thanks!

[Steve]

Hello Wayde,

Judging by the error, the problem is with expression used in filter/grouping/sorting of a CrossTab and not with the report parameters directly. Our suggestion is to revise these expressions and verify if they are correct in the context of a report parameter containing multiple values and allowance of null (i.e. respective IsNull checks have to be in place, see Functions).

All the best,
Steve
the Telerik team

[Wayde]

First a correction of the sql statement I'm using (again, @CountryID is a multi-value parameter built from another sql datasource) :

"select * from Countries where @CountryID is null or CountryID in (@CountryID)"

The problem does not appear to be with null checking.  My results are:
1. Click the NULL checkbox (thus setting the value to null) ...the above works

2. Select ONE value ... the above sql query works

3. Select TWO OR MORE values ... the sql query gives me the exception I specified in my original post.

If the problem is with the query or some kind of null checking I need to do ... why is it that the first two cases succeed and only the last fails?

Thanks - Wayde

[Steve]

Hi Wayde,

Checking for null was only one of the suggestions. Most likely you have an expression in the CrossTab that uses the CountryID report parameter and this expression is no longer valid when an array of values is used as condition.

Greetings,
Steve
the Telerik team

[Wayde]

Actually, the CrossTab does not use the CountryID parameter.

I also think the query is fine.

The problem I speculate has something to do with have a multi-value parameter with AllowNull = True.  I'm trying now to fix what is fed into the SQL statement by editing the value used depending on whether or not there are multiple values selected or not

Right now I have this ...

Name DbType Value
-----------------------------------------------------------------------------------
@CountryID Int32 =iif(Parameters.Indexes.Value.Length > 0,Parameters.Indexes.Value, null)


This is not working.  Here is what happens:

- If I check the Allow Null checkbox it works ... @CountryID is set to null as expected
- If I select ONE value it works ... @Country is set to Parameters.Indexes.Value as expected
- If I select TWO OR MORE VALUES ... it fails.  Why? I don't know

I change my SQL to look like this:

"select * from Countries where CountryID IN (isnull(@CountryID,CountryID))"

The error I get when I pass in MULTIPLE VALUES is ... "The isnull function requires 2 argument(s)"

I'm not sure what is happening here ... but it looks like when multiple values are specified the @CountryID isn't being set to something like "1,2,3" as I would expect.

Any ideas?  I've been dealing with this forever.

Thanks

[Steve]

Hello Wayde,

I've prepared a sample project that works correctly. Please find it attached and should you continue to have problems, provide us with a sample that shows the problem.

Greetings,
Steve
the Telerik team

[Bryan Hughes]

I think the main problem is using length function on a Int32.

[Cory Seaman]

I figured out the solution to this after encountering it myself and seeing no good Telerik response.  With a multi-select, Telerik basically just dynamically replaces your single parameter with a comma-delimited list of params in the SQL, assuming that you'll always put it within a T-SQL "IN ()" function.  Your version, which is:

• ... WHERE @CountryID IS NULL OR CountryID IN (@CountryID)

...ends up throwing an error because Telerik turns it into this:

• ... WHERE @CountryID1, @CountryID2 IS NULL OR CountryID IN (@CountryID1, @CountryID2)

To enable your multi-select parameter to be "ignored" by the query if it's NULL, the only way I've found is to use the T-SQL COALESCE() function in your query, like so:

• ... WHERE COALESCE( @CountryID, NULL) IS NULL OR CountryID IN (@CountryID)

Now the @CountryID can be dynamically replaced with multiple values and it evaluates as you intend...and without fear of a syntax error.

Telerik should include this as a best practice when users want to enable NULL as an option to have a parameter "ignored" in a query's WHERE clause.

[Stef]

Hello Cory,

Thank you for your input.

First let me point that the query is not well structured, if  you expect a collection from the parameter (CountryID in (@CountryID)) the checking for null should handle collection as well - Concat, Coalesce.
So indeed this is one possible solution on query level.
We can suggest you to move this filtering on report level, where the expression will look as follows:

this.Filters.Add(new Telerik.Reporting.Filter("=IIf( Parameters.ContactID.Value is Null,1, Fields.ContactID)", Telerik.Reporting.FilterOperator.In, "=IIf( Parameters.ContactID.Value is Null,1, Parameters.ContactID.Value)"));

The expression is created with the help of the the built-in reporting functions.

Let us know if you have any further concerns.

Greetings,
Stef
the Telerik team

[Patrick Saunders]

Cory thanks for your post, this was exactly the issue I was running into and COALESCE fixed it.
1st time in 10 years of SQL Server Ive found a use for COALESE! :)

Regards, Patrick.

[Joe Kowalski]

Cory, thank you SO MUCH for posting this, it was driving me nuts that I couldn't figure out how to make it work properly.

[Jayaram Krishnan]

Thanks Cory.I was going to post a thread and then found this solution.

This can be tested in t-sql:

select 'A' where 1,2 is null -- this will generate the error mentioned, so telerik is replacing the param value with comma separated values

select 'A' where COALESCE( 1,2, NULL) IS NULL -- this works and will evaluate to false so it will pick the values in the 'in' clause

Madani

[Inayat Walli]

Hi Cory from 2018:)

Your solution still actual, thank you very much, really appriciate your help!

[Cory Seaman]

Glad to see this has been helpful to a few people over the years! :)

[Cory Seaman]

Nice to see that it's been helpful to a few people over the years! :)

[Cory Seaman]

Nice to see that it's been helpful to a few people over the years!

[Jon]

Thanks so much Cory!! This answer is still helpful in 2024! 

@TelerikTeam please add this solution to your documentation!