Best way to generate fixed length reference number using identity

• Daniel Taylor-446457

  Mr or Mrs. 500

  Points: 503

  More actions

  October 19, 2018 at 3:23 am

  #379906

  Hi,

  I have a table with column [CustomerID] [int] IDENTITY(1,1).
  I would like to create a fixed width 'user friendly' refernec number e.g. CN000001, CN000002 etc...
  It can be done in a VIEW ... but id like in the main table.

  This got me wondering what the best way to do this might be.
  The trigger below works ... but is there a trick im missing with Identity, computed columns etc... or a more efficent trigger?

  Any advice welcomed
  Thanks

  /*
  Triggers uses column ([CustomerID] [int] IDENTITY(1,1) )
  and creates a user friendly Customer Number e.g. CN000123
  */

  CREATE TRIGGER [tr_tDeals_cnCustID] ON [FedCapDB_SQL_TEST].[dbo].[tCustomer]
  AFTER INSERT
  AS
  BEGIN
      SET NOCOUNT ON;

      UPDATE [tCustomer]
      SET cnCustomerID = 'CN' + RIGHT('00000' + CAST(t.[CustomerID] AS VARCHAR(8)), 5)
      FROM [tCustomer] t
      WHERE t.cnCustomerID IS NULL
  END

• Thom A

  SSC Guru

  Points: 99137

  More actions

  October 19, 2018 at 4:54 am

  #2010384

  Why not use a PERSISTED Computed Column? In a very simple sense:

  CREATE TABLE SomeTable (ID int IDENTITY(1,1),

           CustomerID AS 'CN' + RIGHT('000000' + CONVERT(varchar(6),ID),6) PERSISTED,

           Firstname varchar(50),

          Surname varchar(50));

  INSERT INTO SomeTable (FirstName, Surname)

  VALUES ('Steve','Jobs'),

     ('Jane','Smith'),

     ('Sally','Jones');

  SELECT *

  FROM SomeTable;

  This would probably be far easier than a trigger.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Daniel Taylor-446457

  Mr or Mrs. 500

  Points: 503

  More actions

  October 19, 2018 at 5:54 am

  #2010395

  This is perfect - seem slicker than a Trigger.
  This is just the kind of reply i was looking for.
  Many Thanks
  Dan