Locking and checkout mechanism Tables:

Clients (20+ fields)
  Id int,
  Name varchar,
  [State] varchar,
  Address varhcar

ClientCheckout
  ClientId
  CheckedOutAt Datetime null


I'm using a separate table for managing the checkout process to avoid putting any locks on the Clients table.

I need to ensure that a client can't be checked out more than once.  Once it is checked out I need to return the id of the client that was checked out.

This is what I came up with but I feel like there should be a better way

Procedure:

Create procedure (@state varchar(2))
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DECLARE @clientid INT

SELECT TOP 1
    @clientid = CustomerId
FROM ClientCheckout
WHERE CheckedOutAt IS NULL AND [state] = @state

BEGIN TRANSACTION T


    UPDATE ClientCheckout
    SET CheckedOutAt = getdate()
    WHERE ClientId = @clientid;
commit transaction t
SELECT
@clientid


I left the initial selection of @clientid outside of the transaction so as not to lock the Clients table.

This seems to work but it doesn't feel quite right.  Suggestions?
You can combine the select and update into a single statement that runs atomically, by doing something like:

CREATE TABLE dbo.Clients
(
    ClientID INT NOT NULL
        CONSTRAINT PK_Clients
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ClientState VARCHAR(255)
);

CREATE TABLE dbo.CallCheckout
(
    ClientID INT NOT NULL
    , CheckedOutAt DATETIME
);

INSERT INTO dbo.Clients(ClientState)
VALUES ('AA')
    , ('AB')
    , ('AC');

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (1, NULL);

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (2, GETDATE());

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (3, NULL);
GO

CREATE PROCEDURE dbo.CheckOut
(
    @state VARCHAR(3)
)
AS
BEGIN
    UPDATE TOP(1) dbo.CallCheckout
    SET CheckedOutAt = getdate()
    OUTPUT inserted.clientid
    FROM dbo.CallCheckout cc
        INNER JOIN dbo.Clients c ON cc.ClientID = c.ClientID
    WHERE cc.CheckedOutAt IS NULL 
        AND c.ClientState = @state;
END
GO

EXEC dbo.CheckOut 'AA';
EXEC dbo.CheckOut 'AC';


The results are:



Notice, I've specified the schema in the CREATE PROCEDURE and UPDATE statements.  You should always specify the schema, even if you only ever use dbo, since it prevents the server from having to make that decision, and potentially making the wrong decision.

Also, please do not use reserved keywords for column names.  I'm talking about [state].  Notice, I've named that column CheckoutState which is not a reserved keyword, and makes the column name more meaningful.