0

Using SQL Server 2019 I have a stored procedure where I pass in values via a user defined table type. One of the columns is a timestamp table type which is used for row version and data concurrency control.

From C# I load a datatable to pass in as a parameter for the UDT, however, I get an error saying I cannot write to a timestamp.

How can I pass the value of the timestamp into the stored procedure via a UDT where it can be used to compare with a timestamp value in the database?

Here’s my UDT:

CREATE TYPE [dbo].[tvp_Eav_Value] AS TABLE(
[EavVl_Id] [uniqueidentifier] NOT NULL,
[EavVl_EavEnt_Id] [uniqueidentifier] NOT NULL,
[EavVl_EavAt_Id] [uniqueidentifier] NOT NULL,
[EavVl_Value] [nvarchar](max) NULL,
[EavVl_LastModifiedUserId] [uniqueidentifier] NULL,
[EavVl_Stamp] [timestamp] NULL
)

And here’s the datatable I’m passing in:

DataTable dt = new DataTable();
dt.Columns.Add("EavVl_Id", typeof(Guid));
dt.Columns.Add("EavVl_EavEnt_Id", typeof(Guid));
dt.Columns.Add("EavVl_EavAt_Id", typeof(Guid));
dt.Columns.Add("EavVl_Value", typeof(string));
dt.Columns.Add("EavVl_LastModifiedUserId", typeof(Guid));
dt.Columns.Add("EavVl_Stamp", typeof(byte[]));

Thank you.

spacedog
  • 446
  • 3
  • 13
  • 1
    Have you tried using a `DateTime` instead of a `byte[]`? How would you expect a `byte[]` to get converted to a time stamp – Flydog57 May 26 '21 at 16:21
  • 1
    you don't assign the value of a `timestamp`; it's controlled by SQL Server. – Thom A May 26 '21 at 16:27
  • 2
    You can't. Heed the error: a `timestamp` (better named `rowversion`) column cannot be set client-side through any means. You should not have a column like that in a table type as you can't meaningfully use it; you can use a `BINARY(8)` and convert it to `ROWVERSION` as needed for comparisons. – Jeroen Mostert May 26 '21 at 16:27
  • 1
    @Flydog57 `timestamp` is a binary value; assigning a `datetime` to it makes no sense. – Thom A May 26 '21 at 16:27
  • 1
    Does this answer your question? [What does a timestamp in T-Sql mean in C#?](https://stackoverflow.com/questions/6334777/what-does-a-timestamp-in-t-sql-mean-in-c) – devlin carnate May 26 '21 at 16:30
  • 1
    Does this answer your question? [How can I include a RowVerson column in a table valued parameter when calling a stored procedure?](https://stackoverflow.com/questions/19729157/how-can-i-include-a-rowverson-column-in-a-table-valued-parameter-when-calling-a) @devlincarnate I think this one is more directly related – Charlieface May 26 '21 at 18:02
  • Thanks All. The comment from @Jeroen is the answer I needed. I just needed a way to move the timestamp value via the table type. – spacedog Jun 03 '21 at 03:35

0 Answers0