title: Delete multiple rows from a table in sql server with a single call from client application
body: <p>Here is my requirement:</p>
  
  <ul>
  <li>I want to delete multiple rows of a table </li>
  <li>I have the list of all ids that needs to be deleted in the client app.</li>
  <li>All I want is to make just a <strong>single call from my C# client app to delete all these records.</strong></li>
  </ul>
  
  <p>What I had earlier:</p>
  
  <p><code>foreach (var id in idList)</code><br>
  <code>{</code><br>
   <code>//Call a Stored Procedure which takes the id</code><br>
     <code>//as parameter and deletes the record</code><br>
  <code>}</code></p>
  
  <p>This is a problem because there is a database hit for each item in loop.</p>
  
  <p>Then I started using the <strong>SQL Server 2008 newly introduced Table Types and Table Valued Parameters</strong> for stored procedures for this.</p>
  
  <ul>
  <li>First have a Table Type created in the database which has a single column (of type of Id)</li>
  <li>Create a new Stored Procedure which accepts this new table type (Table Valued Parameter)</li>
  <li>In the Client code, create a list/Datatable with all the ids to be deleted.</li>
  <li>Make a single call to the database to make use of the new stored Procedure.</li>
  </ul>
  
  <p>It is pretty straight forward in the client code, which goes something like this:</p>
  
  <p><code>SqlParameter parameter = new SqlParameter();</code><br>
  <code>parameter.SqlDbType = System.Data.SqlDbType.Structured;</code>  </p>
  
  <p>OK - Though I achieved what I set for, this definitely does not work for SQL server versions before 2008.</p>
  
  <p>So what I did was to introduce a fall back mechanism:  </p>
  
  <ul>
  <li>I introduced a Stored Procedure that takes <strong>comma separated id values</strong></li>
  <li>My client code will create such comma separated ids, and then simply pass it to the new stored proc.</li>
  <li>The Stored Proc will then split all those ids, and then call delete one by one.</li>
  <li>So in a way, I have achieved what I had set for even on SQL Server version prior to 2008: <strong>delete multiple rows with a single database hit.</strong></li>
  </ul>
  
  <p>But I am not that convinced with the last approach I took - A quick search on the internet did not reveal anything much. </p>
  
  <p>So can somebody tell me if I am doing the right thing with comma separated ids and all.<br>
  What is the usual best practice to delete multiple records from the client app with a single database hit, provided the ids are known.  </p>
  
  <p>Thanks in advance.</p>