CODEWITHSUNDEEP
sql-server
Shivangi Mittal
Shivangi Mittal

Reputation: 19

Generate auto sequence in SQL server as AB001...AB999

I want to generate SQL sequence for the table(table name - Employee).

column(Emp_Title) as

AB001.....AB999 once the value reaches to AB999

it should automatically start with AC001...AC999 and from here again start with AD001......AD999. The process will go upto ZZ001......ZZ999.

This sequence I will be using to insert a new employee record in my employee table.

Example As - 

INSERT INTO Employee VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'EmpName', 'EmpDesignation', 'EmpSalary')

Upvotes: 0

Views: 311

Answers (1)

Chad Estes
Chad Estes

Reputation: 381

You could place an Identity column on this table and then a computed column to transform the identity value into the format you want. Something like the following should work:

CREATE TABLE [dbo].[Employee](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [EmpName] [varchar](50) NOT NULL,
   [EmpDesignation] [varchar](50) NOT NULL,
   [EmpSalary] [Money] NOT NULL,
   [Emp_Title]  AS ((CHAR((65)+([ID]/(1000))/(26))+CHAR((65)+([ID]/(1000))%(26)))+FORMAT((([ID]-1)%(1000)+1),'000'))       
) ON [PRIMARY]

Or you could create a view that would manipulate your ID field to display only in the format for which you are looking.

In either case you'd need to put a constraint in place on the ID to keep it under the max value that would generate the ZZ999 ID, if my calcs are correct that would be 675999.

Upvotes: 1

Related Questions