Hi!

First of all, you do not need to insert records into the table if table is not there. Just create the simple SQL script that do this fr all tables from start (when dtaabase is empty) and thats all. This will give you the speed. The most quick and good way is following:

UPDATE id 
  SET @nreturn = LastID, LastID = LastID + 1
  WHERE TableName  = @tablename

In a single UPDATE command record will be locked automatically so another user querying the key value at the same time will wait until first user finish work, so no duplicates will be generated.

In your code duplicates will occur, unless you use option for the first select (that query the ID from table) to lock record immediately and a transaction.

All this comes from the Thread#533898 in SQL Server forum.

HTH.

>I have a table to get the nextID (I can't use the id column property on a couple tables since data already exists). The SQL Server table is called SCHID with a layout of:
>
>

>RECORDID I <Indenty Column>
>TableName  VarChar (5)
>LastID   I
>
>

>
>I'd like to be able to create a stored procedure on the SQL server that I can pass it the table name and get the next id, if there is no record in for that table insert a record and return 1 as the lastid
>
>Here is what I have that kinda starts the process:
>
>

>CREATE procedure sp_Next_id
>@tablename varchar,
>@nreturn int output
>as
>declare @nLastID int
>select nlastid=lastid from schid
>	where tablename=@tablename
>
>if @@rowcount = 0
>   set @nreturn=1
>   set @nlastid=1
>   insert into schid (tablename,lastid) values (@tablename,@nlastid)
>   GO
>

>
>I Call it with:
>
>

>lccmd=[exec sp_next_id "CT_PAT",?@nNextID]
>? SQLEXEC(gnConnHandle,lcCmd)
>

>
>This starts to work, but always only stores the 1st character of the table name to the schid table, thus if I run the same command again, it doesn't find a match.
>
>Here is what I wanted, but it doesn't pass the syntx check in when building the stored procedure in SQL
>
>

>CREATE procedure sp_Next_id
>@tablename varchar,
>@nreturn int output
>as
>declare @nLastID int
>select nlastid=lastid from schid
>	where tablename=@tablename
>
>if @@rowcount = 0
>   set @nreturn=1
>   set @nlastid=1
>   insert into schid (tablename,lastid) values (@tablename,@nlastid)
>   GO
>else
>   set @nReturn=@nlastID+1
>   update schid set lastID=@nLastID
>
>
>

>
>Any Help Appreciated