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 a001,a002,a003 .... 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(PersonID,JobDescription) VALUES ( 1 , ' Become President of the USA' ) INSERT Job(PersonID,JobDescription) 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