Attendance_Add_tr.sql

create procedure dbo.Attendance_Add_tr
  @OrganisationId int,
  @PersonId int,
  @AttendanceDate date
as

-- MSSQL equivalent of disabling chained transaction
set implicit_transactions off;

set nocount on;

------------------------------------------------------------
-- Validation block
------------------------------------------------------------
set transaction isolation level read committed;

if @@TRANCOUNT > 0
begin
  exec ThrowError_OpenTransaction @@PROCID;
end

if not exists (
  select 1
  from dbo.Organisation
  where OrganisationId = @OrganisationId
)
begin
  return 9; -- Organisation does not exist
end

if not exists (
  select 1
  from dbo.Person
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
)
begin
  return 8; -- Person does not exist
end

if exists (
  select 1
  from dbo.Attendance
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and AttendanceDate = @AttendanceDate
)
begin
  return 7; -- Attendance on the given date has already been recorded.
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 dbo.Organisation
  with (holdlock)
  where OrganisationId = @OrganisationId
)
begin
  rollback;
  return 9; -- Organisation does not exist
end

if not exists (
  select 1
  from dbo.Person
  with (holdlock)
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
)
begin
  rollback;
  return 8; -- Person does not exist
end

if exists (
  select 1
  from dbo.Attendance
  with (holdlock)
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and AttendanceDate = @AttendanceDate
)
begin
  rollback;
  return 7; -- Attendance on the given date has already been recorded.
end

insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
select @OrganisationId, @PersonId, @AttendanceDate;

commit;

Attendance_Get.sql

create procedure dbo.Attendance_Get
  @OrganisationId int,
  @PersonId int,
  @AttendanceDate date
as

set nocount on;
set transaction isolation level read committed;

select OrganisationId, PersonId, AttendanceDate
from dbo.Attendance
where OrganisationId = @OrganisationId
  and PersonId = @PersonId
  and AttendanceDate = @AttendanceDate;

if @@ROWCOUNT != 1
  return 1;
else
  return 0;

GetObjectName_fn.sql

create function dbo.GetObjectName_fn (
  @ObjectId int
)
returns nvarchar(257)
as
begin

return object_schema_name(@ObjectId) + '.' + object_name(@ObjectId);

end

ThrowError_OpenTransaction.sql

create procedure dbo.ThrowError_OpenTransaction (
  @ProcId int
)
as
begin

declare @ErrorMessage nvarchar(512) = concat(
  'The procedure ''', dbo.GetObjectName_fn(@ProcId), ''' is a transaction, which is atomic. ',
  'It has been called within an open transaction, which would render it a non-transaction. ',
  'This is not allowed.'
);

throw 100000, @ErrorMessage, 0;

end

ThrowError_UtilityTransactionRequiresOpenTransaction.sql

create procedure dbo.ThrowError_UtilityTransactionRequiresOpenTransaction (
  @ProcId int
)
as
begin

declare @ErrorMessage nvarchar(512) = concat(
  'The procedure ''', dbo.GetObjectName_fn(@ProcId), ''' is a utility transaction. ',
  'It must be called from within an open transaction.'
);

throw 100001, @ErrorMessage, 0;

end

Hi Derek,

I've now cleaned this thread up. I've quoted most of the messages in the modelling-centric discussion here: DanielLoth/ShootingClubDatabase#2

Cheers,
Dan