Hi!

Id below is an ID table that consist of the keyname field (name of the table for id value) and the IDVal field (the new ID number).

CREATE PROCEDURE MyNewid @tcKeyWord varchar(30)
AS
DECLARE @nnId int

BEGIN TRANSACTION
SELECT @nnId = IDVal
  FROM Id (HOLDLOCK)
  WHERE keyname = @tcKeyWord
IF NOT (@@rowcount = 1)
BEGIN
    INSERT INTO Id (KeyName, IDVal) values (@tcKeyWord,1)
    select @nnId = 1
END
UPDATE Id
  SET IDVal = @nnId + 1
  WHERE keyname = @tcKeyWord
COMMIT TRANSACTION

SELECT @nnId

Return the ID value from there as you need, you can use also RETURN that is better for use in the SP.

HTH.

>I have a situation where I need to generate a next number where I can't use the identity column of a table.
>
>Can anyone give me sample code on how doing this using an NEXT_NO table, handling locking, transaction etc ... ?