Joaquim,

No, it doesn't have to be a list of integer keys, but you'd have to adjust your UDF to handle something other than integer keys.

~~Bonnie



>Thank you Kevin,
>
>After reading the samples you pointed me i was wondering how LINQ could help me.
>And then i see this post.
>
>One last question: it must be a list of integer keys?
>
>Thank you.
>
>Joaquim
>
>>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