ADO.NET TableAdapter parameters

Question

I have a query that I wish to run through an ASP.NET TableAdapter that contains an 'IN' clause which is to receive it's values through a parameter.

My question is, how do I specify this parameter? I thought of writing the conditional statement like this:

AND b.group_category_id in (@ParamList)

where the @ParamList is a String of the parameters, e.g. "4,12,45,23" but since the specified id is an Integer, it complains that it cannot convert String to Integer. This makes sense, but is there any way to specify such a list in a SQL statement in an ASP.NET TableAdapter?

Answer 1

You might have a look at http://dotnet.org.za/johanvw/archive/2008/06/13/mssql-split-function.aspx and pass it indeed as a string. Kind of a workaround than a solution. But that would only be usable if you use MSSQL.

Answer 2

You could pass @ParamList through as a comma-delimited string, parse the string and insert the results into a #table, then use IN to search the #table:

AND b.group_category_id in (select group_category_id from #group_category_id_list)

Apart from this your options are limited, unless you want to use dynamic SQL (exec() statement), but I'd advise you to avoid that if possible.

Answer 3

One workaround I've seen:

WHERE charindex(',' + cast(b.group_category_id as varchar) + ',', @ParamList) > 0

In this case the @ParamList would be a string in the form ",4,12,45,23,"

It's not "pretty", but preserves the parameter and benefits of a compiled query. Since you are searching for numbers, the sub-string guarantees a unique match.