SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
BEGIN TRAN 
update table_num_schema set
    @nextvalue = next_value = next_value + 1;
where
    name = 'ShipID' 

COMMIT TRAN GRAB_SHP_NBR 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

set @nextvalue = @nextvalue - 1;

declare @MyOut table  ( next_value int )

update table_num_scheme
set
	next_value = next_value + 1
output
	inserted.next_value
into 
	@MyOut
where
	name = 'ShipID'

select next_value from @MyOut

CREATE PROCEDURE dbo.GetNextID
/****************************************************************************************
 Purpose:
 This stored procedure is used to get a NextID for the table identified by the @KeyID
 parameter.  It will "reserve" a block of ID's according to the @IncrementValue parameter.
 The @NextID returned is always the first ID of a reserved block of numbers. The reserved
 block size defaults to 1.

 Usage:
 EXEC @return = dbo.GetNextID @KeyID,@IncrementValue,@NextID=@NextID OUTPUT

 Outputs:
 1. Returns a -1 if error and 0 if success.
 2. @NextID will be a -1 if an error occured.  Otherwise, it will contain the first 
    NextID of the requested block of NextID's.

 Notes:
  1. This procedure has been enhanced compared to the original...
     a. The UPDATE statement sets both the @NextID variable and the NextID column in the
        NextID table eliminating the need for a separate SELECT from NextID after the
        UPDATE.
     b. Because of (1.a) above, there is no longer a need for a transaction.  If the 
        UPDATE didn't work, there is no need for a ROLLBACK because nothing was updated.
     c. Previous error handling did not correctly return the invalid KeyID if present.
     d. A test has been added to ensure a negative value for @IncrementValue was not 
        passed in.
     e. A test to ensure that @NextID was correctly updated has been added.
     f. Repairs to the previous error routines have been made so that the values returned
        to @@ERROR and @@ROWCOUNT are correctly used by more than one statement.

 Revisions:
 REV 01 - 01 Mar 2005 - Kalpa Shah, Jeff Moden --Rewrite original
 REV 02 - 06 Feb 2010 - Jeff Moden -- Removed all company references
****************************************************************************************/
--=======================================================================================
--      Define the I/O parameters used by this procedure
--=======================================================================================

--===== Declare the passed parameters
        @KeyID          INTEGER,        --Identifies table to get the NextID for
        @IncrementValue INTEGER = 1,    --Number of NextIDs to "reserve"
        @NextID         INTEGER OUTPUT  --Returns start # of block of IDs
     AS

--=======================================================================================
--      Main body of procedure
--=======================================================================================

--===== Suppress auto-display of row counts for appearance and speed
    SET NOCOUNT ON

--===== Declare variables local to the loop
DECLARE	@MyError    INTEGER      --Holds @@ERROR for additional processing
DECLARE @ErrMessage VARCHAR(100) --Holds calculated error messages because RaisError
                                 --cannot calulate messages on the fly.
DECLARE @MyRowCount INTEGER      --Hold @@ROWCOUNT for additional processing

--===== Preset @NextID to an error condition
    SET @NextID = -1  --Defaults don't work consistently on OUTPUT parameters

--===== If the increment is not greater than zero, raise and error and exit immediately
     IF @IncrementValue <= 0
  BEGIN --Start of error processing
        --===== Process errors (RaisError cannot do calcs for error message)
            SET @ErrMessage = 'The NextID row could not be updated. '
                            + 'Increment was set to ' 
                            + CONVERT(VARCHAR(11),@IncrementValue) + '.'
      RAISERROR (@ErrMessage,1,1)
         RETURN -1 --Returns an error indication to calling procedure
    END --End of error processing

--===== Update the correct NextID row according to the KeyID passed in.
     -- Sets @NextID and the column to the previous value + the increment
     -- simultaneously so we don't need to read from the NextID table to
     -- get the value of @NextID in the following steps. 
 UPDATE dbo.NextID WITH (UPDLOCK)				
    SET @NextID = NextID = NextID + @IncrementValue
  WHERE KeyID = @KeyID

     -- Get the error value and rowcount
 SELECT @MyError = @@ERROR, @MyRowCount = @@ROWCOUNT 	

--===== Check for errors, a rowcount of 1, and a non-default value for @NextID
     IF @MyError    <> 0 --An error did occur
     OR @MyRowCount <> 1 --The row was not updated
     OR @NextID     = -1 --A new value for @NextID was not returned
  BEGIN --Start of error processing
        --===== Process errors (RaisError cannot do calcs for error message)
             IF @MyError <> 0 --Error occured
                SET @ErrMessage = 'The NextID row could not be updated.'
           ELSE --1 row or @NextID was not not updated
                SET @ErrMessage = 'The NextID row could not be updated.  KeyID ' 
                                + CONVERT(VARCHAR(11),@KeyID) 
                                + ' may not exist.'
      RAISERROR (@ErrMessage,1,1)
         RETURN -1 --Returns an error indication to calling procedure
    END --End of error processing

--===== Calculate and return the first number in the block of reserved NextID's
     -- to the @NextID output parameter
 SELECT @NextID = @NextID - @IncrementValue

--===== Return a "success" indication to the calling procedure
 RETURN 0
GO