Parameter Tips and Tricks (Volume 2)

Multi-value Parameters and Null

The Problem

One of the questions I hear most often is (and I have asked it myself a few times) is, “Why can I not have a multi-value parameter that also allows NULL?”

It is a good question, and one that does not have a simple answer. The easiest way to justify this restriction is that the meaning of such a parameter is unclear. If you consider a SQL query that makes use of a multi value parameter, it might look like this:

                SELECT * FROM MyTable WHERE MyField IN (@MyParam)

Passing non-null values in here works quite well by substituting @MyParam with a comma separated list of values. Expanded, that might look like this:

                SELECT * FROM MyTable WHERE MyField IN (‘Value1’,’Value2’)

You might think that passing null in here would work, but unfortunately you would be wrong. The first issue is how to translate NULL from something Reporting Services (RS) understands to something the database understands. Is NULL in RS equivalent to System.DbNull, an empty string, or something else? Does it need quotation marks around it? The second problem is that even if we find the right translation, we are still not out of the woods. This query does not do what you likely want:

                SELECT * FROM MyTable WHERE MyField IN (‘Value1’,NULL) ß Not going to work

So where does that leave us? To return the values from a table where a field is null, we need to use the IS operator with NULL :

SELECT * FROM MyTable WHERE MyField IS NULL

Of course now we have lost the rest of our filter. Combining the two does not work:

SELECT * FROM MyTable WHERE MyField IN (@MyParam) OR MyField IS NULL

This would always return the filtered values AND the records where MyField is NULL.

One Solution

The following trick takes a bit of setting up, but it works quite well. The first thing you want to do is create a query where we can compare apples to apples. In other words, we want stop treating NULL differently than any other value. One way you can do this is to force NULL into a string:

SELECT * FROM MyTable WHERE

  CASE

WHEN Customers.Region IS NULL THEN 'NULL'

ELSE Customers.Region END IN (@Region)

Now we need to pass NULL in to this query as a string. Let us assume we have a multi-value report parameter whose available values come from another query:

                SELECT DISTINCT MyField FROM MyTable

RS is going to do some massaging of this data to remove the NULL values before presenting the available values to the user. We need to add that back in not as NULL, but as the string ‘NULL’ instead. We can modify that query to do this:

                SELECT DISTINCT MyField FROM MyTable

                UNION

                SELECT ‘NULL’ AS MyField

Unfortunately, NULL now appears alphabetized under “N” in your list. We can tweak this by introducing a sort order in a nested query:

SELECT * FROM

(SELECT DISTINCT Customers.Region, 1 AS SortOrder FROM Customers

UNION

 SELECT 'NULL' AS Region, 0 AS SortOrder) WithNull

ORDER BY SortOrder ASC

You can now set the default value of this parameter to ‘NULL’ if you like.

[Thanks to BB who suggested using ' NULL' (note the space) instead; this causes it to be sorted to the top of the list without the need for the sort order in a nested query.]

This sample works for the simple SQL query case, but since there are many flavours of SQL running on a variety of DBMSs and, of course, non-SQL queries it is difficult to cover all cases. This article is meant as a jumping-off point to get you started down the right path.