Passing array of numbers to SQL query

9 Sep 2006 4:20 PM

jrogers.sw

I am using an objectdatasource with a .Net 2.0 ASP page.

The SQL for the tableadapter needs to use the IN operator as in
WHERE job_id in (111, 222, 333, 444, 555)
Job_id is a DBType Decimal and ProviderType Number

I have set the default value for the parameter to be
19620,19610,19580,19550 for testing

However, .Net strips the , and turns it into one large number.

How do I specify that it is rather an array or list of numbers,
esentially passing an array of numbers to the parameter?

9 Sep 2006 8:56 PM

Rob MacFadyen

Hi,

The short answer is... you can't. There is no SQL mechanism for passing an
array for use with IN (...).

If the number of list of numbers is small you could just do each element as
a specific parameter. If the list is large you'll have to store the values
in a table and change the IN (...) to be "N ((SELECT n FROM t WHERE
....))"... or maybe an INNER JOIN (once the numbers are stored in a table...
why not do a join instead of an IN).

Regards,

Rob MacFadyen

<jrogers***@gmail.com> wrote in message

Show quote

news:1157818845.409180.167050@e3g2000cwe.googlegroups.com...
>I am using an objectdatasource with a .Net 2.0 ASP page.
>
> The SQL for the tableadapter needs to use the IN operator as in
> WHERE job_id in (111, 222, 333, 444, 555)
> Job_id is a DBType Decimal and ProviderType Number
>
> I have set the default value for the parameter to be
> 19620,19610,19580,19550 for testing
>
> However, .Net strips the , and turns it into one large number.
>
> How do I specify that it is rather an array or list of numbers,
> esentially passing an array of numbers to the parameter?
>

11 Sep 2006 12:31 PM

Jim Andersen

jrogers***@gmail.com wrote:

> The SQL for the tableadapter needs to use the IN operator as in
> WHERE job_id in (111, 222, 333, 444, 555)
>
> How do I specify that it is rather an array or list of numbers,
> esentially passing an array of numbers to the parameter?

Pass it as a string. Then let the stored procedure that returns the data,
parse out the individual items.
There are some issues with this approach....

Search google groups in comp.databases.ms-sqlserver. I remember Erland
Sommarskog (or something like that) has a HP about this specific problem.

/jim