Blank Multi-value Parameters

Hi,

Lets say I have two multi-value parameters on a report. One is required, the other is not. The multi-value parameter that is not required is allowed to be blank. In the WHERE clause of my query I wanted to do this:

Code Snippet

AND
EXISTS
(SELECT * FROM SomeTable AS ST_1
WHERE (MyCode IN (@.My_Codes2)) OR @.My_Codes2 IS NULL)

Of course I can't do that since multi-value parameters are not allowed to be null. I see that they are allowed to be blank, but how do I check for that? I tried

Code Snippet

OR @.My_Codes2=''

That doesn't work. I can set a default value of 'None' and say:

Code Snippet

OR @.My_Codes2='None'

Unfortunately this doesn't work. As long as the default value is 'None' it's fine. When I fill in the parameter I get the following error:

An expression of non-boolean type specified in a context where a condition is expected, near ','.

Please note that I am not using a TSQL stored procedure here. It's just a plain old embedded SQL statement.

Thanks,

DD

Darren:

This is kind of klunky, but if @.My_Codes2 is a character datatype you might be able to use the COALESCE function to dodge around this. Instead of a where clause like this:

Code Snippet

WHERE (MyCode IN (@.My_Codes2)) OR @.My_Codes2 = '')

You might be able to get by with something like this:

Code Snippet

WHERE (MyCode IN (@.My_Codes2)) OR COALESCE(@.My_Codes2, '') = '')

As an example, I created a report based in an derived table of integers 1-5 with a multi-value parm @.my_codes2:

Code Snippet

select myCode
from ( select 1 as myCode union all select 2 union all
select 3 union all select 4 union all select 5
) as numbers
where myCode in (@.My_Codes2) or coalesce(@.My_Codes2, '') = ''

I turned on PROFILER so that I could catch the interpreted code that was sent to SQL Server from Report Services so that I could examine what Report Services is doing under the hood. When I leave pass a blank for @.My_Codes2 the code gets interpreted into the following and returns all of the data in the table:

Code Snippet

exec sp_executesql N'select myCode
from ( select 1 as myCode union all select 2 union all
select 3 union all select 4 union all select 5
) as numbers
where myCode in (@.My_Codes2) or coalesce(@.My_Codes2, '''') = ''''',
N'@.My_Codes2 nvarchar(1)',@.My_Codes2=N' '

/*
myCode
--
1
2
3
4
5
*/

When I pass a parameter of '1' for my parameter it gets interpreted similarly and returns only the matching row:

Code Snippet

exec sp_executesql N'select myCode
from ( select 1 as myCode union all select 2 union all
select 3 union all select 4 union all select 5
) as numbers
where myCode in (@.My_Codes2) or coalesce(@.My_Codes2, '''') = ''''',
N'@.My_Codes2 nvarchar(1)',@.My_Codes2=N'1'

/*
myCode
--
1
*/

When I actually pass in '1' and '3' as multiple parameters, the statement gets interpreted a little differently and returns the correct two matching rows:

Code Snippet

select myCode
from ( select 1 as myCode union all select 2 union all
select 3 union all select 4 union all select 5
) as numbers
where myCode in (N'1',N'3') or coalesce(N'1',N'3', '') = ''

/*
myCode
--
1
3
*/

I am not using the coalesce with intentions of catching NULLS but rather am using it is a receiver that will handle the different interpretations of the parameters. The main problem with this is that this will return the entire target list if you pass multiple parameters and the first parameter is a blank.

|||

Hi Kent,

A multi-value parameter can't be null so COALESCE doesn't work unfortunately.

The way I got around it was by using another parameter:

Code Snippet

WHERE MyCode IN (@.My_Codes2))

OR

@.Use_Other_Codes = 1

Klunky but it works.

Thanks,

Darren

|||Darren -

I think I am trying to do the same thing as you <http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1885823&SiteID=1>. Do you think you could take a look and help me out? I'm not sure I'm fully understanding how you got around your problem.

Thanks,
Josh