Attendance_Add_tr with lost update protection

Messages.sql

--------------------------------------------------
-- Transaction misuse messages
--------------------------------------------------
exec sp_addmessage
  @msgnum = 100001,
  @msgtext = N'%s: is a transaction, which is Atomic. It has been called from within an open transaction, which would render it a non-transaction. That is not allowed.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

exec sp_addmessage
  @msgnum = 100002,
  @msgtext = N'%s: is a utility transaction. It must be called from within a open transaction.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

exec sp_addmessage
  @msgnum = 100003,
  @msgtext = N'%s: does not allow implicit transactions. Ensure that implicit transactions are not enabled (run command ''set implicit_transactions off;'') before trying again.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

--------------------------------------------------
-- Organisation messages
--------------------------------------------------
exec sp_addmessage
  @msgnum = 100100,
  @msgtext = N'%s: Organisation does not exist.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

--------------------------------------------------
-- Person messages
--------------------------------------------------
exec sp_addmessage
  @msgnum = 100200,
  @msgtext = N'%s: Person does not exist.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

exec sp_addmessage
  @msgnum = 100201,
  @msgtext = N'%s: The Person record has been modified by another user. Please refresh your data.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

--------------------------------------------------
-- Visit messages
--------------------------------------------------
exec sp_addmessage
  @msgnum = 100300,
  @msgtext = N'%s: Visit does not exist.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

exec sp_addmessage
  @msgnum = 100301,
  @msgtext = N'%s: Visit has already been recorded for the given date.',
  @severity = 16,
  @lang = 'us_english',
  @with_log = 'false',
  @replace = 'replace';

go

Visit_Add_tr.sql

create procedure Visit_Add_tr
  @OrganisationId int,
  @PersonId int,
  @VisitDate date,
  @PersonLastUpdatedDtm datetimeoffset(3)
as

set transaction isolation level read committed;

declare @ProcName sysname = object_name(@@procid);
declare @CurrentLastUpdatedDtm datetimeoffset(3);

------------------------------------------------------------
-- Validation block
------------------------------------------------------------

-- Ensure we are not running in an already-opened transaction.
if @@TRANCOUNT > 0
begin
  raiserror (100001, -1, -1, @ProcName);
  return 1;
end

-- Ensure we are not running in 'implicit transactions' mode
-- (also known as 'chained transactions' on some platforms)
if (select @@OPTIONS & 2) <> 0
begin
  raiserror (100003, -1, -1, @ProcName);
  return 1;
end


if not exists (
  select 1
  from Organisation
  where OrganisationId = @OrganisationId
)
begin
  raiserror (100100, -1, -1, @ProcName); -- Organisation does not exist
  return 1;
end

select @CurrentLastUpdatedDtm = UpdatedDtm
from Person
where OrganisationId = @OrganisationId
  and PersonId = @PersonId;

if (@@ROWCOUNT != 1)
begin
  raiserror (100200, -1, -1, @ProcName); -- Person does not exist
  return 1;
end

if (@CurrentLastUpdatedDtm != @PersonLastUpdatedDtm)
begin
  raiserror (100201, -1, -1, @ProcName); -- Person has been updated by another user
  return 1;
end

if exists (
  select 1
  from Visit
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and VisitDate = @VisitDate
)
begin
  raiserror (100301, -1, -1, @ProcName); -- Visit on the given date has already been recorded.
  return 1;
end

------------------------------------------------------------
-- Execute block
------------------------------------------------------------

-- This 'set transaction isolation level' statement is an
-- alternative to specifying the 'holdlock' int as done below.
set transaction isolation level serializable;

begin transaction;

if not exists (
  select 1
  from Organisation
  with (holdlock)
  where OrganisationId = @OrganisationId
)
begin
  rollback;
  raiserror (100100, -1, -1, @ProcName); -- Organisation does not exist
  return 1;
end

select @CurrentLastUpdatedDtm = UpdatedDtm
from Person
with (updlock)
where OrganisationId = @OrganisationId
  and PersonId = @PersonId;

if (@@ROWCOUNT != 1)
begin
  rollback;
  raiserror (100200, -1, -1, @ProcName); -- Person does not exist
  return 1;
end

if (@CurrentLastUpdatedDtm != @PersonLastUpdatedDtm)
begin
  rollback;
  raiserror (100201, -1, -1, @ProcName); -- Person has been updated by another user
  return 1;
end

if exists (
  select 1
  from Visit
  with (updlock)
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and VisitDate = @VisitDate
)
begin
  rollback;
  raiserror (100301, -1, -1, @ProcName); -- Visit on the given date has already been recorded.
  return 1;
end

update Person
set UpdatedDtm = GETUTCDATE()
where OrganisationId = @OrganisationId
  and PersonId = @PersonId;

insert into Visit (OrganisationId, PersonId, VisitDate)
values (@OrganisationId, @PersonId, @VisitDate);

commit;

return 0;

Hi Derek,

So how is it that you see my comments on this Gist page

I'm subscribed to the Gist. There's a button at the top with a bell icon.

Dan

So how is it that you see my comments on this Gist page

I'm subscribed to the Gist. There's a button at the top with a bell icon.
...

I am Subscribed to this Gist.

There are other issues, GitHub is plain clunky. Eg. if I enter @D in a PullRequest comment, it shows a list and I can choose @Daniel.Loth, which shows up bold @Daniel.Loth, and I don't have to reload the page: new comments are already there when I go to the page. If I enter @D in a Gist comment, it does nothing. Hence here it is regular "Dan", and a reload to check for new comments.

Anyway, too small to worry about. Could be Firefox.

Cheers
Derek

There is one more issue with your Vist_Add_tr.sql that I do not understand: the use of GETUTCDATE() instead of GETDATE(), and the messing around with it in the code.

Either could be used. I use UTC time out of habit.

When you mentioned messing around with it in code, are you referring to this?

select @CurrentLastUpdatedDtm = UpdatedDtm
from Person
where OrganisationId = @OrganisationId
  and PersonId = @PersonId;

if (@@ROWCOUNT != 1)
begin
  raiserror (100200, -1, -1, @ProcName); -- Person does not exist
  return 1;
end

if (@CurrentLastUpdatedDtm != @PersonLastUpdatedDtm)
begin
  raiserror (100201, -1, -1, @ProcName); -- Person has been updated by another user
  return 1;
end

If so, I was just using this approach to check both existence and currency with a single select statement.