I need to provide a solution to the problem of passing multiple parameters to a series of reporting stored procedures in SQL Server 2008. The parameters are going to be user preferences and may consist of lists where the user has selected more than one type of value (e.g. multiple months). There may be as many as 30 different parameters and maybe 60% of them will contain multiple selections.
So far I have 3 options.
Pass in the values as normal parameters with a set interface - this seems to fail quite quickly as I would need to pass collections (multiple months would be a good example).
Pass in the parameters as a single XML fragment. This method is already used here although I'm not sure if it can cope with multiple elements of the same type (e.g. as in months above). The code uses DynaFilter to parse the XML - I've never heard of it and can't find any reference on the internet. The developer that wrote the code is back tomorrow so I'll have more info then.
Use table value parameters - I'm only just beginning to look at these but they look promising and seem to offer good performance.
We're using SQL Server 2008 with an ASP.Net MVC front end. We could move to 2012 if needed.
I've started and will continue to do more research on the best way to deal with this problem but would value any opinions on the best way forward and if there are any other options available.
Thanks in advance.