есть скрипт:

declare @RecordID as identifiers
declare @InvoiceNumber as identifiers

set @recordID = xxxx
set @InvoiceNumber = null

select top 1 @InvoiceNumber = a.InvoiceNumber
from MDSInvoiceNumbers a
    left outer join MDSCustomerBalance b
        on a.InvoiceNumber = b.InvoiceNumber
where b.BalanceID is null
order by a.InvoiceNumber

if isnull(@InvoiceNumber,0) = 0
    begin
        insert into MDSInvoiceNumbers (null_field) values(null)
        set @InvoiceNumber = @@identity
    end

update MDSCustomerBalance set InvoiceNumber = @InvoiceNumber where RecordID = @RecordID

Этот скприпт может одновременно вызываться во многих сессиях из процедур и триггеров, необходимо чтобы в первом селекте выбиралась одна запись и лочилась на время пока InvoiceNumber не попадет в MDSCustomerBalance таблицу.

Как это лучше всего сделать?

Это описание таблиц если надо:

CREATE TABLE [dbo].[MDSInvoiceNumbers](
    [InvoiceNumber] [dbo].[identifiers] IDENTITY(10001,1) NOT NULL,
    [null_field] [char](1) NULL,
    CONSTRAINT [PK_MDSInvoiceNumbers] PRIMARY KEY CLUSTERED ([InvoiceNumber] ASC)
) ON [PRIMARY]

CREATE TABLE [dbo].[MDSCustomerBalance](
    [BalanceID] [dbo].[identifiers] IDENTITY(1,1) NOT NULL,
    [InvoiceNumber] [dbo].[identifiers] NULL,
    ...,
    CONSTRAINT [PK_MDSCustomerBalance] PRIMARY KEY CLUSTERED ([BalanceID] ASC)
) ON [PRIMARY]

ALTER TABLE [dbo].[MDSCustomerBalance]  WITH NOCHECK ADD  CONSTRAINT [FK_MDSCustomerBalance_MDSInvoiceNumbers] FOREIGN KEY([InvoiceNumber])
REFERENCES [dbo].[MDSInvoiceNumbers] ([InvoiceNumber])
GO