Exception when updating row with rowversion?

Question

I have a table that looks like this :

CREATE TABLE [dbo].[akut_prioritering]
(
    [behandling_id] [int] NOT NULL,
    [akutstatus] [int] NOT NULL,
    [nasta_dag] [bit] NOT NULL,
    [sort_order] [bigint] NOT NULL,
    [rowversion] [timestamp] NOT NULL,

    CONSTRAINT [XPKakut_prioritering] 
        PRIMARY KEY CLUSTERED ([behandling_id] ASC)
) ON [PRIMARY]

And then I have this stored procedure that tries to update rows in this table :

ALTER PROCEDURE [dbo].[akutlistaSave] 
    @behandlingSortOrder dbo.akutlista_sortorder_tabletype READONLY
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @behandlingId INT;
    DECLARE @sortOrder BIGINT;
    DECLARE @rowversion ROWVERSION;

    DECLARE sortOrderCursor CURSOR LOCAL SCROLL STATIC FOR
        SELECT behandling_id, sort_order FROM @behandlingSortOrder

    OPEN sortOrderCursor

    BEGIN TRAN

        FETCH NEXT FROM sortOrderCursor INTO @behandlingId, @sortOrder, @rowversion

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF EXISTS(SELECT * 
                      FROM akut_prioritering ap 
                      WHERE ap.behandling_id = @behandlingId 
                        AND ap.rowversion = @rowversion)
            BEGIN
                UPDATE akut_prioritering
                SET sort_order = @sortOrder
                WHERE behandling_id = @behandlingId;
            END
            ELSE
            BEGIN
                RAISERROR ('Rowversion not correct.', 16, 1);
            END

            FETCH NEXT FROM sortOrderCursor INTO @behandlingId, @sortOrder, @rowversion
        END

        CLOSE sortOrderCursor

        SELECT 
            ap.behandling_id, ap.rowversion 
        FROM 
            akut_prioritering ap 
        INNER JOIN 
            @behandlingSortOrder bso ON ap.behandling_id = bso.behandling_id;

        DEALLOCATE sortOrderCursor
END

The inparameter type looks like this :

CREATE TYPE [dbo].[akutlista_sortorder_tabletype] AS TABLE 
            (
                [behandling_id] [int] NULL,
                [sort_order] [bigint] NULL,
                [rowversion] [timestamp] NULL
            )

When running this I get a SqlException :

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

From what I understand the rowversion column should be updated with a new value automatically, there is no reason in my case to set it manual.

There's not much point in using a stored procedure to perform optimistic updates in a batch, if you end up performing *individual* updates using a cursor. This ends up being *slower* than sending individual `UPDATE` statements. A better option would be to join between the target table and the input and update only the rows that have matching rowversions. *Then* select and return all those that *don't* have matching rowversions — Panagiotis Kanavos, Jul 14 '17 at 09:55
An ORM would produce similar code *without* the penalty introduced by the cursor. — Panagiotis Kanavos, Jul 14 '17 at 09:57

score 3 · Accepted Answer · answered Jul 14 '17 at 09:48

You can't set the rowversion value in dbo.akutlista_sortorder_tabletype because it is not updateable: it is auto generated

However, rowversion (a.k.a deprecated timestamp) is simply a (var)binary(8) with some special rules. You can define and set a (var)binary(8) in dbo.akutlista_sortorder_tabletype and compare on that in the UPDATE

From the first link

A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion column is semantically equivalent to a varbinary(8) column.

score 0 · Answer 2 · answered Jul 14 '17 at 09:49

0

It looks like you are trying to insert a timestamp value in a custom table type and then passing that to your stored procedure. As your error suggests, you cannot insert explicit timestamp values into timestamp columns.

You will need to find a different way of passing you table values to this stored procedure to work.

answered Jul 14 '17 at 09:49

iamdave

12,023
3
24
53

That's not what the OP asks. The code doesn't seem to be modifying the rowversion anywhere – Panagiotis Kanavos Jul 14 '17 at 09:51
@PanagiotisKanavos The stored procedure takes in a custom table type as the parameter that includes a `timestamp` column, which if not `insert`ed into directly renders any `timestamp` comparison useless. – iamdave Jul 14 '17 at 09:53
1

that's not the problem. Comparisons are allowed. The problem is that the *TVP* can't even be filled by the *client* because `rowversion` can't be modified – Panagiotis Kanavos Jul 14 '17 at 10:03
@PanagiotisKanavos So you could say the problem is that the OP is "*trying to `insert` a `timestamp` value in a custom table type*" per the first line of my answer? – iamdave Jul 14 '17 at 10:05

Exception when updating row with rowversion?

2 Answers2