0

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?

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117

1 Answers1

0

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.

Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Can the table-valued parameter create tables "on the fly"? Because if 20 people run this report simultaneously, I don't think I can pre-set the tables because one user might overwrite another. This report can potentially be used by over 1,000 people simultaneously. – Johnny Bones Jun 10 '15 at 19:53
  • It's a parameter, the number of concurrent users does not effect it. Think of it like you are passing a two dimensional array to a function parameter in c# (it's basically the same thing) - the number of times you use that function doesn't effect the array itself... – Zohar Peled Jun 11 '15 at 06:52