Passing datatable/dataset/array as query parameters to SQL Server

  • Thread starter Thread starter Art
  • Start date Start date
A

Art

Hi folks,

I've got an app that retrieves a datatable from a third-party database
(the db is hosted by a partner company; we get the data via a web
service) containing around 100 rows of product SKUs.

I need to take those 100 SKUs, pass them to our SQL Server db to
determine which SKUs also exist in our database, then return a dataset
to the user which lists the full 100 SKUs, along with a 'yes/no' column
showing which SKUs exist in our db.

My question:

What's the fastest way to pass the 100 SKUs to our db and check to see
if each SKU exists in our db? I've seen various options which seem to
boil down to the following:

-- create a stored procedure that takes one SKU and checks our db; run
the SPROC 100 times;

OR

-- send the SKUs to the db in a comma-delimited string; have the sproc
parse the string and stick the SKUs in a temp table; check the db temp
table against the db;

Any thoughts on which of the above is faster? Easier? Is there another
method I should look at?

Thanks!

Art
 
I dont know why people use SQL server, but I guess it'll be very hard work
to do it
if u have unique identifier for each row [unique primary key] then I gues
you can use couple of select statement using IN operator [separate product
IDs by comma e.g IN (230,231,232) and so on

I guess the stored procedure will be faster as its compiled to the native
SQL thing

Or if you are using Oracle 9i or greater you can use Merge statements to do
the whole thing in one query.


Webmaster of Code Master
http://www.code-master.net
 
Back
Top