The first thing you need is a table to hold your generated keys:
CREATE TABLE [dbo].[IdGenerator](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Pk] [varchar](5) NOT NULL
)
INSERT INTO IdGenerator (Pk) VALUES ('A0001')
It contains 2 columns. Id (the primary key, used to order your keys) and the key you want!
Then you need this script to generate a new Key base on the previous one:
(See comments for explanation)
DECLARE @PK varchar(5)
DECLARE @Character char(1)
DECLARE @Number int
SET @PK = (SELECT TOP 1 PK FROM IdGenerator Order by Id desc)
SET @Character = (SELECT SUBSTRING (@PK ,1, 1))
SET @Number = (SELECT SUBSTRING (@PK ,2, 4))
SET @Number = @Number + 1
IF @Number > 9999
BEGIN
SET @Number = 1
SET @Character = char(ascii(@Character)+1)
END
SET @PK = @Character + REPLACE(STR(@Number, 4), SPACE(1), '0')
INSERT INTO IdGenerator (PK) VALUES (@PK)
SELECT @PK
For this to work, you will have to add the first value (A0001) manually. And you
You can put this code in a stored procedure.