GenCode (auto)

1. Dùng table
   CREATE TABLE test
   (
   sname varchar(15),
   ID int identity(1,1),
   student_ID AS 'S' + RIGHT(DATENAME(yy,GETDATE()),2) +RIGHT('0000'+CAST(ID AS varchar(4)),4)
   )
   Test:
   insert into test (sname) values (1)
   --
   select *from test
   --
   sname ID student_ID
   1 1 S090001
2. Dùng function
   create function CustomerNumber (@id int)
   returns char(5)
   as
   begin
   return 'C' + right('0000' + convert(varchar(10), @id), 4)
   end
   --
   Gán hàm trên vào cột:
   alter table Customers add CustomerNumber as dbo.CustomerNumber(dbID)
   hoặc gán vào trigger
   
   Dùng trigger
   alter Customers add CustomerNumber varchar(10)
   
   create trigger Customers_insert
   on Customers after insert
   as
   update Customers
   set Customers.customerNumber = dbo.CustomerNumber(Customers.dbID)
   from Customers inner join inserted
   on Customers.dbID= inserted.dbID
3. Dùng proc
   create procedure prcGenCode
   @codeno char(6) OUTPUT
   as
   select @codeno = Max(Ccodeno) from testtable
   select @codeno =
   case
   when @codeno >=0 and @codeno < 9 then '00000' + convert(char,@codeno + 1)
   when @codeno >=9 and @codeno < 99 then '0000' + convert(char,@codeno + 1)
   when @codeno >=99 and @codeno < 999 then '000' + convert(char,@codeno + 1)
   when @codeno >=999 and @codeno < 9999 then '00' + convert(char,@codeno + 1)
   when @codeno >=9999 and @codeno < 99999 then '0' + convert(char,@codeno + 1)
   when @codeno >=99999 then convert(char,@codeno + 1)
   end
   return