title: Passing table valued parameter to stored proc with different number of fields
body: <p>I have a requirement where I need to pass a table valued parameter to a stored procedure. Clients need the flexibility to add fields to the table valued type without affecting C# code (even though it requires deleting and recreating all dependent stored procedures). Is it even possible to add fewer columns to the DataTable than what is in the data type?
  We are using Microsoft SQL Server 2008 R2.</p>
  
  <p>I have tried this:</p>
  
  <p><strong>SQL:</strong></p>
  
  <pre><code>CREATE TYPE [dbo].[MyDataType] As Table
  (
      ID INT NULL,
      Name NVARCHAR(50) NULL
  )
  
  CREATE PROCEDURE [dbo].[MyProcedure]
  (
      @myData As [dbo].[MyDataType] Readonly
  )
  AS
  Begin
      Select * FROM @myData
  End
  </code></pre>
  
  <p><strong>C#:</strong></p>
  
  <pre><code>DataTable myDataTable = new DataTable("MyDataType");
  myDataTable.Columns.Add("Id", typeof(Int32));
  myDataTable.Rows.Add(1);
  myDataTable.Rows.Add(2);
  
  SqlParameter parameter = new SqlParameter();
  parameter.ParameterName = "@myData";
  parameter.SqlDbType = System.Data.SqlDbType.Structured;
  parameter.Value = myDataTable;
  command.Parameters.Add(parameter); 
  </code></pre>
  
  <p>I am getting following exception:</p>
  
  <pre><code>A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
  Additional information: Trying to pass a table-valued parameter with 1 column(s) where the corresponding user-defined table type requires 2 column(s).
  </code></pre>