what is the best practice for creating auto generate IDs

-- 

First Create The Tables :


Generate Student Ids :

create table  CSE
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'CSE'+'000'+convert(varchar,ID)
when 2 then 'CSE'+'000'+convert(varchar,ID)
else 'CSE'+convert(varchar,ID)
end
)

--insert into CSE default values

create table  ECE
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'ECE'+'000'+convert(varchar,ID)
when 2 then 'ECE'+'000'+convert(varchar,ID)
else 'ECE'+convert(varchar,ID)
end
)

--insert into ECE default values

create table  EEE
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'EEE'+'000'+convert(varchar,ID)
when 2 then 'EEE'+'000'+convert(varchar,ID)
else 'EEE'+convert(varchar,ID)
end
)

--insert into EEE default values

create table  IT
(
Id int identity(1,1),
--BranchName varchar(10),
Pid as case  len(Id) when 1 then 'IT'+'000'+convert(varchar,ID)
when 2 then 'IT'+'000'+convert(varchar,ID)
else 'IT'+convert(varchar,ID)
end
)

--insert into IT values('IT')

create table  MECH
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'MECH'+'000'+convert(varchar,ID)
when 2 then 'MECH'+'000'+convert(varchar,ID)
else 'MECH'+convert(varchar,ID)
end
)

--insert into MECH default values

create table  CIVIL
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'CIVIL'+'000'+convert(varchar,ID)
when 2 then 'CIVIL'+'000'+convert(varchar,ID)
else 'CIVIL'+convert(varchar,ID)
end
)

--insert into CIVIL default values

create table  PROD
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'PROD'+'000'+convert(varchar,ID)
when 2 then 'PROD'+'000'+convert(varchar,ID)
else 'PROD'+convert(varchar,ID)
end
)


create table Temp123(StudentId varchar(10))

------------------------------------------------------------------

After Creating Tables , Use This Sp.

If U Pass The Student Id like 'CSE' The Automatically StudentId 'CSE00001' Is generate, visvarsa Which Branch Name U can Pass In @studentId That Student Id Generated.(Student Id is Identity)

CREATE  PROC [dbo].[Usp_GenerateID]
(
	@StudentId VARCHAR(10)
)

-- EXEC Usp_GenerateID 'CSE'

	AS

	SET NOCOUNT ON

	BEGIN

IF ((SELECT BranchName  FROM ColzBranchDetails WHERE BranchName = @StudentId)='CSE')
BEGIN 
INSERT INTO CSE DEFAULT VALUES
INSERT Temp123 
SELECT Pid FROM CSE  WHERE Pid not in (SELECT StudentId FROM Temp123 WHERE StudentID = CSE.Pid)
END
					
ELSE

IF ((SELECT BranchName  FROM ColzBranchDetails 
WHERE BranchName = @StudentId )='ECE')
BEGIN 
INSERT INTO ECE DEFAULT VALUES
INSERT Temp123
SELECT Pid FROM ECE  WHERE Pid not in  (SELECT StudentId FROM Temp123 WHERE StudentID = ECE.Pid)
END
				
ELSE
					
IF ((SELECT BranchName  FROM ColzBranchDetails 
WHERE BranchName = @StudentId )='EEE')
BEGIN 
INSERT INTO EEE DEFAULT VALUES
INSERT Temp123
SELECT Pid FROM EEE  WHERE Pid not in  (SELECT StudentId FROM Temp123 WHERE StudentID = EEE.Pid)
END

ELSE
					
IF ((SELECT BranchName  FROM ColzBranchDetails 
WHERE BranchName = @StudentId )='IT')
BEGIN 
INSERT INTO IT DEFAULT VALUES
INSERT Temp123
SELECT Pid FROM IT  WHERE Pid not in  (SELECT StudentId FROM Temp123 WHERE StudentID = IT.Pid)
END
					
ELSE
					
IF ((SELECT BranchName  FROM ColzBranchDetails 
WHERE BranchName = @StudentId )='MECH')
BEGIN 
INSERT INTO MECH DEFAULT VALUES
INSERT Temp123
SELECT Pid FROM MECH  WHERE Pid not in  (SELECT StudentId FROM Temp123 WHERE StudentID = MECH.Pid)
END
					
ELSE
IF ((SELECT BranchName  FROM ColzBranchDetails 
WHERE BranchName = @StudentId )='CIVIL')
BEGIN 
INSERT INTO CIVIL DEFAULT VALUES
INSERT Temp123
SELECT Pid FROM CIVIL  WHERE Pid not in  (SELECT StudentId FROM Temp123 WHERE StudentID = CIVIL.Pid)
END
					
ELSE
					
IF ((SELECT BranchName  FROM ColzBranchDetails 
WHERE BranchName = @StudentId )='PROD')
BEGIN 
INSERT INTO PROD DEFAULT VALUES
INSERT Temp123
SELECT Pid FROM PROD  WHERE Pid not in  (SELECT StudentId FROM Temp123 WHERE StudentID = PROD.Pid)
END
	
END
	
SET NOCOUNT OFF

</pre>