Hi, Joaquim,

Bonnie Berent pointed out to me that I mis-read your question. Apparently what you wanted to do was query a SQL database table against a variable list of integer keys.

Here's the way I handle this (this assumes you're using SQL 2005):

1) I will pass an XML representation of the memory datatable from .NET to a stored procedure.
2) The stored procedure receives the XML string as a parameter (and the string could have 1 key, 10 keys, 100 keys, etc.)
3) Inside SQL Server, I have a generic UDF that uses the new XQUERY capabilities to read the XML into a table variable.
4) The stored proc queries against the back-end database, and then does a JOIN against the table variable from step 3


Here is a generic UDF for step 3, in T-SQL 2005. This will convert any XML string to a table var.

CREATE FUNCTION [dbo].[XML2TableVar] 
(  @XMLString XML) 
RETURNS 
@tPKList TABLE  ( PKValue int) 
AS 
BEGIN 
            INSERT INTO @tPKList 
              SELECT Tbl.Col.value('.','int') 
                  FROM   @XMLString.nodes('//PKValue') Tbl( Col ) 
   RETURN 
END 

And so you could use it like the following

DECLARE @XMLString XML 
SET @XMLString ='<Customers> 
                     <Customer> 
                         <PKValue>2</PKValue> 
                          <PKValue>5</PKValue> 
                     </Customer> 
               </Customers>' 


-- Perform a JOIN using the UDF 

SELECT Customers.* FROM Customers 
                        JOIN [dbo].[XML2TableVar] (@XMLString) CustList 
                                    ON Customers.CustomerPK = CustList.PKValue

So all you'd need to do is pass the XML representation of a datatable with an integer column called PKValue.

Let me know if that helps...

Also....SQL Server 2008 will actually allow you to pass an ADO.NET Data Table as a table-type parameter, which will essentially eliminate steps 2 and 3 above....but that won't be available until next year.

Kevin