SQL server 2005 sequence implement

This is the text that describes the sequence in SQL server 2005.

Table Name:	MG_SEQUENCE_KEY	Index
Database:	MS SQL Server 2005
Table Description:	Table to store sequence id used for Intenal user and super admin user. Generate TIN value with this id.
Key Types:	Primary key - PK, Foreign key - FK
Subject Area:	Security
Logical Name	Physical Name	Data Type	Size	Nullable?	Key Type	Description
KEY_NAME	KEY_NAME	varchar	30	No	PK	KEY_INTERNAL_USER_ID or KEY_SUPER_ADMIN_ID
CURRENT_VALUE	CURRENT_VALUE	bigint		No		the current value of the id
VALUE_STEP	VALUE_STEP	int		No		the increase value for each step

 Service code to get the next key value:

/**
* generate the MetGuardRefId of internal user
* @param 
* @return String MgRefId
* @throws ServiceFailException
*/
public String getInterMgRefId()throws ServiceFailException{
String  mgRefId = null;
try {
Long nextKey = accountDAO.getNextKey(Constants.KEY_INTERNAL_USER_ID);
Long temp = 10000000+nextKey;
String key = temp.toString().substring(1, 8);
mgRefId = "MG"+ key;
return mgRefId;
} catch (DAOException e) {
IBW_LOG.error(LOG_CONTEXT, EVENTS.ADHOC, "AccountBusinessImpl call deleteUser failed", e);
throw new ServiceFailException(e.getMessage(),e);
}
}

/**
* generate the MetGuardRefId of super user
* @param 
* @return String MgRefId
* @throws ServiceFailException
*/
public String getSuperMgRefId()throws ServiceFailException{
String  mgRefId = null;
try {
Long nextKey = accountDAO.getNextKey(Constants.KEY_SUPER_ADMIN_ID);
Long temp = 1000000000+nextKey;
String key = temp.toString().substring(1, 10);
mgRefId = key;
   return mgRefId;
} catch (DAOException e) {
IBW_LOG.error(LOG_CONTEXT, EVENTS.ADHOC, "AccountBusinessImpl call deleteUser failed", e);
throw new ServiceFailException(e.getMessage(),e);
}
}

 

The DAO code to get the next key value:

 

      public static final String GET_NEXT_KEY ="{call NEXT_KEY(?,?)}";

 

The parameter keyType can be either KEY_INTERNAL_USER_ID orKEY_SUPER_ADMIN_ID.

/**

       * Get Sequence key for internal user registration

       * @param keyType 

       * @return String

       * @throws DAOException

       */

      public LonggetNextKey(String keyType)throws DAOException{

            Connection conn = null;

            CallableStatement stmt = null;

            ResultSet rs = null;               

            Long nextKey = null;

            try {

                  conn = ConnectionSQLServerFactory.getConnection();

                  conn.setAutoCommit(false);

                  conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

              

                  String sql = SQLConstants.GET_NEXT_KEY;

                  stmt = conn.prepareCall(sql);

                  stmt.registerOutParameter(1,Types.BIGINT); 

                  stmt.setString(2, keyType);

                  stmt.executeUpdate();

                  nextKey = stmt.getLong(1);   

                  DbUtils.commitAndClose(conn);

            return nextKey;

        } catch (Exception e) {

            DbUtils.rollbackAndCloseQuietly(conn);

           iBwLog.error(logContext, EVENTS.ADHOC, "MemberDAOImplcall getNextKey failed", e);

            throw newDAOException(e.getMessage(),e);

        } finally {

            DbUtils.closeQuietly(conn,stmt, rs);

        }        

      }

 

 

Script:

 IF OBJECT_ID('MG_SEQUENCE_KEY')>0
    DROP TABLE MG_SEQUENCE_KEY
GO

CREATE TABLE MG_SEQUENCE_KEY(

KEY_NAME VARCHAR(30) NOT NULL,

CURRENT_VALUEBIGINT,

VALUE_STEP INT DEFAULT 1 NOT NULL,

CONSTRAINTMG_SEQUENCE_KEY_PK PRIMARY KEY(KEY_NAME)

);

 

Init data as below:

 

INSERT INTO MG_SEQUENCE_KEY VALUES('KEY_INTERNAL_USER_ID',1,1);

INSERT INTO MG_SEQUENCE_KEY VALUES('KEY_SUPER_ADMIN_ID',1,1);

 

 

Procedure name : NEXT_KEY

Script:

CREATE PROCEDURE NEXT_KEY @NEXT_VALUE BIGINT OUT , @KEY_NAME VARCHAR(30)

AS

BEGIN

    IF@KEY_NAME IS NULL

        RAISERROR ('KEY NAME IS NULL.', 16, 11);

    DECLARE@V_NEXT_VALUE BIGINT;

    DECLARE@ERROR_MESSAGE VARCHAR(1024)

    SELECT@NEXT_VALUE = CURRENT_VALUE FROM MG_SEQUENCE_KEY WITH(UPDLOCK) WHERE KEY_NAME = @KEY_NAME

    IF @@ROWCOUNT = 0

    BEGIN

        SET@ERROR_MESSAGE='INVALIDKEY NAME IS PROVIDED, THE KEY NAME IS: ' +@KEY_NAME

        RAISERROR (@ERROR_MESSAGE , 16, 12);

    END;

    UPDATEMG_SEQUENCE_KEY SET CURRENT_VALUE = CURRENT_VALUE +VALUE_STEP WHERE KEY_NAME = @KEY_NAME ANDCURRENT_VALUE = @NEXT_VALUE;

   

    RETURN@NEXT_VALUE

END

GO

declare @nextkey BIGINT
Exec @nextkey = NEXT_KEY  @nextkey, 'KEY_INTERNAL_USER_ID'
print @nextkey