Auto generate id

Question

Hi all This is adam I have a table by name record in which i have a coloumn id i want to create id dynamically such as a001a002a003 .... Please help me its urgent.. Thanks Subhan

Manikavelu Velayutham · Answer

Hope the following script will help you to generate the unique id Create Table CREATE TABLE Person( ID INT IDENTITY ( 1  1 ) NOT NULL PRIMARY KEY  [Name] VARCHAR ( 100 ) UNIQUE NOT NULL ) -- We will add some sample data... INSERT Person([Name]) VALUES ( ' Richard Nixon' ) INSERT Person([Name]) VALUES ( ' Bill Clinton' ) GO CREATE FUNCTION udf_SET_JobCode( @PersonID INT  @ID INT ) RETURNS VARCHAR ( 10 ) AS BEGIN -- Get the Person referenced by @PersonID DECLARE @Person VARCHAR ( 100 ) = ( SELECT [Name] FROM Person WHERE ID = @PersonID) -- A VARCHAR copy of the @ID input DECLARE @RET VARCHAR ( 10 ) = CAST(@ID AS VARCHAR ( 10 )) -- CHAR Length of the @RET variable DECLARE @LEN INT = LEN(@RET) SET @RET = UPPER( LEFT (@Person 3 )) + -- Magic (see BOL for STUFF(@RET 1  0  LEFT ( ' 0000000'  7 - @LEN)) -- Function -- definitions/syntax RETURN @RET END -- Now the all important Job table that calls our UDF as a reference CREATE TABLE Job( ID INT Identity ( 1  1 ) NOT NULL PRIMARY KEY  PersonID INT NOT NULL FOREIGN KEY REFERENCES Person(ID) JobCode AS dbo.udf_SET_JobCode(PersonID ID) -- UDF Reference JobDescription VARCHAR ( 200 ) NOT NULL ) INSERT Job(PersonIDJobDescription) VALUES ( 1  ' Become President of the USA' ) INSERT Job(PersonIDJobDescription) VALUES ( 1  ' Become Infamous' ) -- Select the data... SELECT * FROM Job GO RESULT: =========================================================== ID PersonID JobCode JobDescription ---- ------------------- ---------- --------------------- 1 1 RIC0000001 Become President of the USA 2 1 RIC0000002 Become Infamous