How to return data culled from multiple multiselect listboxes in a C#/SQL Server environment

Question

I'm writing a report in C# and my report form has 10 multiselect list boxes. I've written a stored procedure in SQL Server that handles this data, however I've got that old "You can't use a parameter in an 'IN' clause" issue. In other words, in SQL Server you can't write:

SELECT * FROM MyTable WHERE MyField IN (@MyParam) 

That doesn't work. So someone suggested Table-Valued Parameters. Apparently, you store the parameters in a sort of temp table and then use that temp table as the basis of your 'IN' clause.

However, I've never seen an example where this is used 10 times. Is this even an option at that point? Is there a better way? And can anyone give me an example/sample code where something like this is done?

Answer 1

Use a table valued parameter to send the data to the stored procedure.
You can use a single parameter with 10 columns, each column represent one of you listboxes, no need to use 10 parameters for that. Just remember to allow null values in the user defined table type.