Passing array of numbers to SQL query

[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?

 

[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

 

[Jim Andersen]

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

 

[Jason Kester]

You'll have to pass the list of Integers to the stored procedure as a
string, then handle them on that side.

The quick and dirty way would be to use dynamic sql to build and
execute your query as a big string, but then you'd have to spend the
rest of your career living in fear that some junior dev will dig up
your hacky code one day and call you on it.

What you'll probably end up doing is building a User Defined Function
in the database that takes your comma-separated list and hands it back
as single-column table that you can join into your query:

WHERE job_id in (select value from dbo.inline_list_split(@integerList)
)

Dig around in the SQL groups to find code to do the splitting for you.

Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/