create table KeyPoolNew (
	ID_KeyPool                      ID                               not null  ,
	ID_Location                     ID                               not null  ,
	NameTable                       varchar( 30 )                      not null  ,
	Value                           ID                                   null  ,
	ValueMax                        ID                                   null  ,
		CONSTRAINT PK_KeyPool PRIMARY KEY CLUSTERED ( ID_KeyPool )  on 'default' 
)
lock datarows
 on 'default'

CREATE PROCEDURE nkp_NewID
	 @idkp int,
     @TableName varchar( 30 ) = null,  
     @LocationID int = null,  
     @NID int output as  
BEGIN   
    DECLARE @CNT INTEGER 
    DECLARE @MinID INTEGER, @vm integer 
    DECLARE @ID_KeyPool INTEGER 
 
    SELECT @NID=Value, @vm = ValueMax 
    FROM KeyPoolNew (index PK_KeyPool)
    WHERE ID_KeyPool = @idkp
    
    
 
    UPDATE KeyPoolNew SET Value=Value+ 1  from Keypoolnew (index PK_KeyPool)
    WHERE ID_KeyPool=@idkp
    
  /*  if @NID = @vm 
    delete from KeyPoolNew (index PK_KeyPool) 
    where ID_KeyPool = @idkp */
    
END

begin tran sss
exec nkp_newID  1 ,NULL,NULL,@m output

begin tran sss
exec nkp_newID  25 ,NULL,NULL,@m output

fid	spid	loid	locktype	table_id	page	row	dbname	class	context
 0 	 13 	 26 	Ex_intent	 1113104025 	 0 	 0 	keeper	Non Cursor Lock               	 
 0 	 13 	 26 	Ex_row-blk	 1113104025 	 6752 	 0 	keeper	Non Cursor Lock               	 
 0 	 14 	 28 	Sh_intent	 1113104025 	 0 	 0 	keeper	Non Cursor Lock               	 
 0 	 15 	 30 	Sh_intent	 32000114 	 0 	 0 	master	Non Cursor Lock

------------------------ Execute ------------------------

QUERY PLAN FOR STATEMENT  1  (at line  1 ).


    STEP  1 
        The type of query is DECLARE.


QUERY PLAN FOR STATEMENT  2  (at line  2 ).


    STEP  1 
        The type of query is BEGIN TRANSACTION.


QUERY PLAN FOR STATEMENT  3  (at line  3 ).


    STEP  1 
        The type of query is EXECUTE.


QUERY PLAN FOR STATEMENT  1  (at line  0 ).


    STEP  1 
        The type of query is DECLARE.


QUERY PLAN FOR STATEMENT  2  (at line  11 ).


    STEP  1 
        The type of query is SELECT.

        FROM TABLE
            KeyPoolNew
        Nested iteration.
        Using Clustered Index.
        Index : PK_KeyPool
        Forward scan.
        Positioning at index start.
        Using I/O Size  2  Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size  2  Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.


QUERY PLAN FOR STATEMENT  3  (at line  17 ).


    STEP  1 
        The type of query is UPDATE.
        The update mode is direct.

        FROM TABLE
            KeyPoolNew
        Nested iteration.
        Using Clustered Index.
        Index : PK_KeyPool
        Forward scan.
        Positioning at index start.
        Using I/O Size  2  Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size  2  Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            KeyPoolNew
        Using I/O Size  2  Kbytes for data pages.

return status =  0 
( 1  rows affected)
------------------------- Done --------------------------

CREATE PROCEDURE nkp_NewID
	 @TableName varchar( 30 ),  
     @lid ID,   
     @NID ID output as  
BEGIN   
    DECLARE @CNT INTEGER 
    DECLARE @vm integer 
    DECLARE @ID_KeyPool ID 
 
    SELECT @NID=Value, @vm = ValueMax,@ID_KeyPool = ID_KeyPool  
    FROM KeyPoolNew (index tb_lc) readpast
    WHERE NameTable = @TableName and ID_location = @lid
    
    if @ID_KeyPool is NULL	begin 
    	waitfor delay '00:00:02'
		SELECT @NID=Value, @vm = ValueMax,@ID_KeyPool = ID_KeyPool  
		FROM KeyPoolNew (index tb_lc) readpast
		WHERE NameTable = @TableName and ID_location = @lid
	end
	if @ID_KeyPool is NULL	begin
		raiserror  27001  
		rollback
		return
	end
	UPDATE KeyPoolNew 
	SET Value = Value +  1  FROM KeyPoolNew 
	WHERE ID_KeyPool = @ID_KeyPool
	
	if @NID = @vm 
	delete KeyPoolNew 
	from KeyPoolNew (index PK_KeyPoolNew)
	where ID_KeyPool = @ID_KeyPool
 END