Create procedure or trigger to automate ID

• nghoiming02

  SSC-Addicted

  Points: 418

  More actions

  February 20, 2004 at 8:28 pm

  #83834

  Dear everyone,

  I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

  I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

  Could anyone know how to do that?? Thanks!!

  From,

  Roy

• nkohad

  SSC Enthusiast

  Points: 109

  More actions

  February 20, 2004 at 11:32 pm

  #495037

  Hi,

  Create a seperate table

  id_tbl

  id_name varchar(20),

  id_prefix char(5),

  id_current_number int

   

  Store your PK id in this table with the prefix string you want and enter by default starting number for the PK.

  Then before insertion in table , generate a unique PK from id_tbl and after generating PK , increment the id_current_number in id_tbl.

  You can also concatenate the julien date in string.

  This is the way ,most of the companies use.

   

  One more way is there , generating the unique string IDs from the Hexadecimals.But I don't know how to generate it. I read it somewhere.

  bye

  Nitin