How to avoid a race condition with cross db proc calls? and other concerns
There is nothing in your SELECT that dictates order at all. It is also not protected from two sessions reading the same row. To see that it is not safe:


Create a throwaway table with a key column.

CREATE TABLE dbo.GeneratedIDs(ID INT PRIMARY KEY);

Run this code in a loop from two different sessions:

SET NOCOUNT ON;

DECLARE @DECLARE @CID INT, @CodeOUt VARCHAR(30);

SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
  FROM dbFoo.dbo.CodeNumbers
  WHERE IsUsed = 0

UPDATE dbFoo.dbo.CodeNumbers
  SET IsUsed = 1
  WHERE CodeNumbersID = @CID 

INSERT dbo.GeneratedIDs SELECT @CID;

GO 100000

Check the output for these - they'll happen:


  Msg 2627, Level 14, State 1
  Violation of PRIMARY KEY constraint 'PK_hexcode'. Cannot insert duplicate key in object 'dbo.GeneratedIDs'. The duplicate key value is (&lt;some value>).


Or you might see deadlocks if you wrap the SELECT/UPDATE in an explicit transaction:


  Msg 1205, Level 13, State 52
  Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.



To get around this problem, and to ensure that the ID you get is the lowest one available, you could do this:

BEGIN TRANSACTION;

SELECT TOP (1) @CID = ...
FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
WHERE IsUsed = 0
ORDER BY CodeNumbersID;

UPDATE ...

COMMIT TRANSACTION;


Note that I added an explicit transaction and also XLOCK/HOLDLOCK hints to prevent two simultaneous sessions from reading the same row. Of course, this has an impact on concurrency (which, unfortunately, is exactly what you want and need here).

Other ways to do this include just updating the row and then using a table variable to capture values from the OUTPUT clause:

DECLARE @x TABLE(CodeOut VARCHAR(30), CID INT);

;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x SET IsUsed = 1
  OUTPUT inserted.CodeValue, inserted.CodeNumbersID INTO @x;

SELECT @CodeOut = CodeOut, @CID = CID FROM @x;


Per Paul's update, yes, you could also do this without the table variable:

;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x 
  SET @CodeOut = x.CodeValue, @CID = x.CodeNumbersID, IsUsed = 1;


(Though I am not all that fond of this syntax; not sure why. Might be the same reason I always forget it exists.)

You could change the caller to expect a resultset instead of two output parameters, but that is extra work too. In both cases you'd still need to ensure you got the lowest ID available, which likely means a CTE with a SELECT and the same hints. Some discussion here. I also talk about a similar approach in this blog post but I didn't get into anything about concurrency and two sessions trying to delete the same row at the same time. Obviously in that case only one of them can win, but with an UPDATE they could both manage to be successful (at least in theory).

To make things easier, you could relax the restriction that the "next" ID handed out is the lowest ID available. But you still need the isolation through the hints to ensure that two simultaneous sessions don't happen to read the same value (which could happen regardless of ordering). Hopefully with a suitable index these won't destroy concurrency.