4

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?

Tomislav Nakic-Alfirevic
  • 10,017
  • 5
  • 38
  • 51
jatto
  • 39
  • 1
  • 1
  • 3
  • Well the error is stating that you're trying to insert a value into an IDENTITY column - that's by default not what you're supposed to be doing. SQL Server should handle the IDENTITY fields by itself. So change your INSERT so that it does **not** supply a value for the IDENTITY column - let SQL Server handle that one. – marc_s Jul 09 '10 at 08:42
  • 2
    Yes, I did that. I supplied a column without a value, but there was still an error. When I remove this column from DataTable then the error is that a column is missing. – jatto Jul 09 '10 at 08:53

2 Answers2

7

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)
KyleMit
  • 30,350
  • 66
  • 462
  • 664
Brad
  • 823
  • 1
  • 9
  • 17
1

If the question is "how do I pass a TVP with a column marked INT IDENTITY(1,1)?" you don't. A TVP is not a table. You may be specifying the TVP values, which means that you don't want an identity on the value that you're supplying.

The table is usually the one that you want to have the IDENTITY declared on.

If the question is "How can I avoid setting the PK in the TVP?" then I would have to ask for more code.

jcolebrand
  • 15,889
  • 12
  • 75
  • 121
  • +1 Great points. There's no value in having a primary key column on data that isn't persisted. You can skip them on the User Defined Table Type and still declare them on the final Table you're inserting into as in [this example](http://stackoverflow.com/q/26938784/1366033) – KyleMit Feb 22 '17 at 16:01
  • 1
    Can you get the IDENTITY back out to the C# code of the new inserted rows? – uriDium Mar 16 '21 at 10:12
  • Absolutely. What's your framework? Sounds like you wanna ask a new question! :D – jcolebrand Mar 16 '21 at 16:22
  • @uriDium, you can use an `OUTPUT` clause as mentioned in this answer on a different question: https://stackoverflow.com/a/100669/3144603 – JohnnyFun Apr 03 '21 at 20:23