Generate AlphaNumeric Identity Code

    In all sorts of companies, employee ID or the ID's of clients from an Online Trading firm, its very common to have an Alphanumeric ID.But from database perspective, it is better to keep a numeric ID. Any way if you like to generate an Alphanumeric Key, then you have to apply your logics because there isn't any built function for this pupose in MS SQL SERVER. Below two methods could do the job:

--------------------------1st Method-----------------------------

CREATE TABLE MyTestTable

(

 ID         INT IDENTITY (1,1)

,UserID     AS 'E'+RIGHT('000000' + CONVERT(VARCHAR,ID),6)

,UserName   VARCHAR(100)

,City       VARCHAR(100)

,ContactNo  VARCHAR(15)

)




--- Insert the
rows into the table




INSERT INTO MyTestTable VALUES
('Amit','Mumbai','88888888')

INSERT INTO MyTestTable VALUES
('Muthu','Chennai','77777777')




---- Output




SELECT * FROM MyTestTable




ID    UserID      UserName City      ContactNo

1     E000001     Amit   Mumbai     
88888888

2     E000002     Muthu  Chennai 77777777

 --------------------------2nd Method-----------------------------

Now if we don't want to keep the Identity Column and only want to keep the Generated Column, then we can get a new ID with the help of below stored Procedure:

CREATE PROC [dbo].[usp_NewCode]

      @InPrefix         VARCHAR(5),

      @InTableName      VARCHAR(100),

      @InColumnName     VARCHAR(100),

      @InIDLength       INT,  

      @OutCode          VARCHAR(20) OUTPUT

AS

BEGIN

   DECLARE @STR
VARCHAR(100)

   ,@INTPART INT, @SQL NVARCHAR(MAX)

   ,@Parameter_Definition NVARCHAR(MAX)

   ,@Zeros VARCHAR(12)

                              

   SET @Zeros = '0000000000000000000000'

                              

   SET @SQL = N'SELECT @MYSTR =
ISNULL(MAX('

   SET @SQL = @SQL + @InColumnName

   SET @SQL = @SQL + '),''00000'') FROM '

   SET @SQL = @SQL + @InTableName

   SET @SQL = @SQL + ''             




                              

  SET @Parameter_Definition
= N'@MYSTR
NVARCHAR(100) OUTPUT'

  EXECUTE SP_EXECUTESQL @SQL,
@Parameter_Definition, @MYSTR = @STR OUTPUT




  SELECT @STR = SUBSTRING(@STR,1+LEN(@InPrefix),LEN(@STR)-LEN(@InPrefix))

  SELECT @INTPART = CONVERT(INTEGER,@STR)

  SELECT @INTPART = @INTPART+1

  SELECT @OutCode = CONVERT(VARCHAR,@INTPART)

  SELECT @OutCode = (@InPrefix +SUBSTRING(@Zeros,1,@InIDLength-LEN(@OutCode)-LEN(@InPrefix))+
@OutCode)




END
 

 This SP can be used for any number of tables within a database, also the length of the ID is variable. Suppose we are creating a new table MyTestTable where we are giving an UserID column which should be of 8 character length and prefixed with 'EMP' :

CREATE TABLE MyTestTable

(

 UserID     VARCHAR(10)

,UserName   VARCHAR(100)

,City       VARCHAR(100)

,ContactNo  VARCHAR(15)

)




--- Insert the
first row




BEGIN TRY

BEGIN TRAN




  DECLARE @ID VARCHAR(20)

  EXEC [USP_NewCode] 'EMP','MyTestTable','UserID',8, @ID OUTPUT




  INSERT INTO
MyTestTable VALUES (@ID,'Amit','Mumbai','88888888')




  COMMIT TRAN

END TRY     

BEGIN CATCH

  ROLLBACK TRAN

END CATCH




--- Insert the
second row




BEGIN TRY

BEGIN TRAN




  DECLARE @ID VARCHAR(20)

  EXEC [USP_NewCode] 'EMP','MyTestTable','UserID',8, @ID OUTPUT




  INSERT INTO
MyTestTable VALUES (@ID,'Muthu','Chaennai','77777777')




  COMMIT TRAN

END TRY     

BEGIN CATCH

  ROLLBACK TRAN

END CATCH




------- Output




SELECT * FROM MyTestTable




UserID      UserName City     ContactNo

EMP00001    Amit      Mumbai   
88888888

EMP00002    Muthu     Chennai  77777777

Reference: Govind Badkur(http://govindbadkur.blogspot.com)