Identity column in a table-valued parameter in procedure, how to define DataTable

Question

Is it possible to pass a parameter of type "table" with a column of type "[int] IDENTITY(1,1)" to a procedure and execute this stored procedure with a DataTable object passed as the input parameter?

I get the following error: "INSERT into an identity column not allowed on table variables. The data for table-valued parameter \"@xxxxx\" doesn't conform to the table type of the parameter."

The only related comment I was able to find was "If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session."

It seems that even though the PK was not set in the table parameter, it gets set automatically at some point. Where does that happen and how can it be avoided?

Answer 1

I had this same problem where we want an identity on the type, but don't want to provide a value. The key is to use a SqlMetaData constructor for that column that sets useServerDefault to true:

According to this article on using user defined table type with identify column in ado net by Tim Van Wassenhove

SQL:

CREATE TYPE [Star].[example] AS TABLE(  
  [Ordinal] [int] IDENTITY(1,1) NOT NULL,  
  [Name] [nvarchar](200) NOT NULL,
)

C#:

var sqlMetaData = new[] 
{  
  new SqlMetaData("Ordinal", SqlDbType.Int, true, false, SortOrder.Unspecified, -1),   
  new SqlMetaData("Name", SqlDbType.NVarChar, 200)
};

sqlRecords = new HashSet<SqlDataRecord>(usersToInclude.Select(user =>
{   
  var record = new SqlDataRecord(sqlMetaData);   
  record.SetString(1, user.Name);   
  return record; 
}));

new SqlMetaData("IdentityField", SqlDbType.Int, true, false, SortOrder.Unspecified, -1)