This is something that has bothered for me for a while. In SQL Server Reporting Services, you write a query against a data source, and that becomes the data set for the report. Brilliant. I like to write the query in Management Studio first, and then paste it across.
But this doesn’t always work, and multi-value parameters is why. Multi-value parameters are those parameters where you’ve ticked the checkbox to say that you can select multiple options. Like in the image there to the right.
Now, in T-SQL, it is not considered valid to write:
WHERE mycol IN (@ListOfMyCols)
It’s just not the way IN works – in this scenario, it would considered the IN list has a single value, and use that. Don’t bother trying a comma-separated string, it will just see if mycol is that same comma-separated string.
But if you’re using SSRS with parameters that can take multiple values, then this is exactly what you do. You have to write your query as:
SELECT ProductSubcategoryID, Name
FROM Production.ProductSubcategory
WHERE ProductCategoryID IN (@Category)
And the system understands that @Category is a multi-value parameter and passes it to the server in an appropriate way.
At this point, I want to quickly advertise SQL Profiler. Make it your friend, so that you can find out how things are working against your database. Particularly with Reporting Services – it can be very good.
SQL Profiler tells me that when I use a single-value parameter in Reporting Services, sp_executesql is being used, passing parameters to a fixed string. Terrific – this is what I want.
But when I use multi-value parameters, I don’t get this behaviour. Instead, it turns out that SSRS passed across a query with the parameters pre-inserted – it used dynamic SQL!
Now, it works fine – but I don’t like this. It could easily bloat the plan cache, and I just feel like it’s the wrong way to do it. I don’t know a better way – perhaps something could be done through enhancing the table-valued parameters feature of SQL Server 2008 – but I’m not going to hold my breath on that – currently it would have to have a table type declared, and then do something like “WHERE mycol IN (select col1 from @tvp)” – but that would have other problems associated with it.
I’m not suggesting that you don’t use multi-value parameters – but just understand what’s going on with the queries.
UK SQL MVP Simon Sabin (and Harpendenonian) posted something around this around filters, and has recently posted more about query parameters, but I thought I’d write some more, as it’s been something I’ve been meaning to blog about for a while.
