loop a 100 time or let SQL server deal with it

[ONeil Tomlinson]

Hi guys I have an application that does something. It generates an XML document with 100-200 items. For each item there is an ID. I need to check if this ID exist in a table in a database. At the end of checking these ID a second XML document must be created with all the ID that was not present in the Database. There are two ways of doing this ( l prefer option 2 but my senior prefers option 1) What do you thing is the best approach. Option 1: Loop through each record in the XML and with each loop make a call to SQL server to check if this ID exist. If the ID does not exist build a list of the non existing ID in some form of collection whcih will later be used for creating the second XML document Option 2: Pass the XML document to SQL server, let SQL server do the checking against all the ID and then return an XML message of the ID that does not exist Thanks Neil

[N a v a n e e t h]

I vote for option 2. SQL server got good support for XML. You can supply a XMLReader which contains your XML and a transact SQL statement can loop through it and if the "id" does not exist in database, put it in a temporary table. Once you finish the loop, return the temporary table as XML (FOR XML AUTO). :)

Navaneeth How to use google | Ask smart questions

[Gaurav Mahajan]

Option 2 works much faster for long run when you create CLR function in sqlserver. Processing of compliled procedures and functions for multiple database read/write commands make it faster. If anybody goes for Option 1, then he may be good Desktop programmer without any knowledge of database and strength of PL/SQL

[ToddHileHoffer]

Option 3. Return two result sets from SQL server. Write a query to put all records in a table variable. Write a second query with the id column via a join. Return both sets to a dataset. Use ADO.Net (such a datatable select method or rowfilter) to check for the id. Then as you loop through your 100-200 records you don't need to hit sql server for each record, just search your datatable.

I didn't get any requirements for the signature