Testing dataset queries which utilize multi-value parameters in SQL Server 2005 Reporting Services


Normally, when you have a dataset query which is filtered by a parameter (like the one below), it’s easy to test it in the query design tool:


SELECT field1,field2 FROM table WHERE field3 = @someParameter


…you simply click the “!” button on the toolbar, and a Query Parameter dialog pops up in which you specify the value for @someParameter. However, what can you do if you’re using a multi-value parameter and therefore need to provide a set of values:


…WHERE field3 IN (@someParameter)


You might want to pass a series of values, like (‘cat’, ‘dog’), etc. Well, there’s unfortunately no good way to do this. The query design tool is based on SQL Server functionality which doesn’t natively support multi-value parameters. I guess you could temporarily hard-code a few values directly inside your search condition (IN list), but that’s about it. Bummer!

Comments (9)

  1. Anonymous says:

    when I use the above statement I get error in where clause near ‘@’ unable to parse query text?????????????

    any idea?

  2. You’re running into one of two problems:

    1. You don’t have multi-valued paramters turned on

    2. You are trying to preview the result of a query with multi-valued parameters turned on (which the post above says won’t work)

  3. Anonymous says:

    I’m confused about how this is supposed to work. I tried the multi-value parameter being passed to a stored proc but it’s not returning anything. When it passes the parameters does it just do it like a string? If so I beleive you would need to do dynamic sql in your query to make it work…

    thoguhts?

  4. Anonymous says:

    Try this:

    WHERE field3 = ?

  5. Anonymous says:

    Multi-valued parameters can only be used with dynamic text SQL queries in SQL 2005 SSRS. You can get around it using your SP and still using a dynamic query to filter the records with an outer WHERE and the IN (@multiparamname) there.

  6. Anonymous says:

    multi-value parameters are strings.

    what is the way to query integer column. Ex:

    where cast (EmloyeeID as varchar) in (@MyParam )

    doesn’t work.

    Plese help

  7. Anonymous says:

    so, how to do pass multivalue parameter without having to type values inside IN ???

  8. valboy says:

    See this link – the solution worked for me (SQL Server 2005)

  9. Tina Arroyo says:

    valboy, where is the link?  I'm using SQL 2014 and interested in knowing how to pass series of values to test a multivalue parameter