create procedure dbo.get_new_inr_procedure 
(
    @old_inr numeric(10)
) 
 AS 
 DECLARE
    @nextnumber NUMERIC(10);
    BEGIN  
    if @old_inr > 0 
      --LOCK TABLE DT820 IN EXCLUSIVE MODE;
      UPDATE DT820 with (XLOCK, HOLDLOCK) SET di008212 = CAST((Select max(di000617) from dt478) AS CHAR) WHERE di008199 = '000443' AND DI008198=(Select di024408 from dt345 where di024408 != '000');
         
      SELECT @nextnumber=CAST(dbo.trim(di008212) as numeric)+1 FROM DT820 WHERE di008199 = '000443' AND DI008198=(Select di024408 from dt345 where di024408 != '000');
      UPDATE DT820 with (XLOCK, HOLDLOCK) SET di008212 = cast((@nextnumber) as char) WHERE di008199 = '000443' AND DI008198=(Select di024408 from dt345 where di024408 != '000');
      
      BEGIN TRANSACTION
      INSERT INTO DT478 
      (DI000617,
      DI000618,
      DI000622,
      DI000623,
      DI000624,
      DI000625,
      DI000626,
      DI000627,
      DI000628,
      DI000629,
      DI000630,
      DI000631,
      DI023519,
      DI023520,
      DI023521,
      DI023620,
      di000632)
      Select 
      @nextnumber,
      DI000618,
      DI000622,
      DI000623,
      DI000624,
      DI000625,
      DI000626,
      DI000627,
      DI000628,
      DI000629,
      DI000630,
      DI000631,
      DI023519,
      DI023520,
      DI023521,
      DI023620,
      di000632 
      FROM DT478
      where di000617=@old_inr;

      Return @nextnumber;
    if @old_inr <= 0 
      Return @old_inr;
    COMMIT TRANSACTION;
End;  
GO
