How to send a Reporting Services multi-value parameter to a SQL Server function?

Question

Hi, I have a report that use a multi-value parameter into a "in" statement in a query. For example (with @areas as the multi-value parameter):

select * from regions where areas in (@areas)

It works perfectly but now I need to send the same parameter to a function in the SQL Server 2005 database:

select name, myFunction(@areas) from regions where areas in (@areas)

The @areas parameter in the function are going to be used in a "in" statement as well. I tried to receive it with a varchar parameter but this causes an error. When I use the SQL Profiler, I see that the parameter is passed in this format:

N''1'',N''2'',N''3''

The specific questions here are, what data type the function parameter @areas must be? And how can I use that parameter into a "in" statement in the function?

Thanks

Answer 1

Generally if you are going to be passing in a list of some type as a parameter, you are going to want to pass it in as a varchar of a large enough length to handle the entirety of it. You could also pass it in as an XML parameter, but I've always preferred using the varchar route and parsing it that way.

Answer 2

Table valued parameters are supported in SQL Server starting version 2008: http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

Answer 3

For sql 2008 there are table valued params as mentioned. Otherwise your only options are to pass it as a packed value (either comma separated variety in varchar or xml), or else populate a temp table and have the function read from the temp table. If you are calling multiple functions with the same large input, the temp table is likely the best route so you don't have to keep parsing it. If the native input is xml (i.e. you have some input from an external partner like facebook) then it's usually best to keep it as xml. If it is just a modest list of ints then a list of comma separated values isn't bad.