Hi Randy,

Another approach is to create a stored procedure that selects the ID table, locks the record, updates the id record, then returns the id to your application to be used to update your field. Here is a stored procedure I have used to work with IDs.

CREATE PROCEDURE sp_newid
    @tcKeyWord varchar(30)
AS
DECLARE @cId       varchar(11),
        @cNewId    varchar(11),
        @cIncProc  varchar(60),
	@nMaxLen   int
BEGIN TRANSACTION
SELECT @cId = Value,
       @cIncProc = IncrementProcedure,
       @nMaxLen = MaxLength
  FROM Id With (TabLockX,HOLDLOCK)
  WHERE keyname = @tcKeyWord
IF (@@rowcount = 1)
  BEGIN
    SELECT @cId = RTRIM(@cId)
    EXECUTE @cIncProc @cId, @nMaxLen, @cNewId OUTPUT
    UPDATE Id
      SET Value = @cNewId
      WHERE keyname = @tcKeyWord
    COMMIT TRANSACTION
  END
ELSE
  BEGIN
    SELECT @cId = 0
--    SELECT @cId = NULL
--    RAISERROR('Unable to locate an entry for %s in the ID table', 16, -1, @tcKeyWord)
    ROLLBACK TRANSACTION
  END
-- Return the id as a cursor
SELECT iid = CAST(@cId AS INT)
GO

>Thanks for the responses Bob, David, and Cathi. I should be able to do it all in one statement. I just don't understand from your code how I can retrieve either the updated key or the key value before the update completed.