Here's code that I think will give you your Next value. I created 3 functions. The table is just my simulation of the table.column with your alpha ids (I used MyTable.AlphaID). I assume that it's as you implied and there is one contiguous block of five-character uppercase alphabetic strings (AlphaID):
IF OBJECT_ID('dbo.MyTable','U') IS NOT NULL
DROP TABLE dbo.MyTable
GO
CREATE TABLE dbo.MyTable (AlphaID char(5) PRIMARY KEY)
GO
-- Play with different population scenarios for testing
INSERT dbo.MyTable VALUES ('ZZZZY')
INSERT dbo.MyTable VALUES ('ZZZZZ')
INSERT dbo.MyTable VALUES ('AAAAA')
INSERT dbo.MyTable VALUES ('AAAAB')
GO
IF OBJECT_ID('dbo.ConvertAlphaIDToInt','FN') IS NOT NULL
DROP FUNCTION dbo.ConvertAlphaIDToInt
GO
CREATE FUNCTION dbo.ConvertAlphaIDToInt (@AlphaID char(5))
RETURNS int
AS
BEGIN
RETURN 1+ ASCII(SUBSTRING(@AlphaID,5,1))-65
+ ((ASCII(SUBSTRING(@AlphaID,4,1))-65) * 26)
+ ((ASCII(SUBSTRING(@AlphaID,3,1))-65) * POWER(26,2))
+ ((ASCII(SUBSTRING(@AlphaID,2,1))-65) * POWER(26,3))
+ ((ASCII(SUBSTRING(@AlphaID,1,1))-65) * POWER(26,4))
END
GO
IF OBJECT_ID('dbo.ConvertIntToAlphaID','FN') IS NOT NULL
DROP FUNCTION dbo.ConvertIntToAlphaID
GO
CREATE FUNCTION dbo.ConvertIntToAlphaID (@ID int)
RETURNS char(5)
AS
BEGIN
RETURN CHAR((@ID-1) / POWER(26,4) + 65)
+ CHAR ((@ID-1) % POWER(26,4) / POWER(26,3) + 65)
+ CHAR ((@ID-1) % POWER(26,3) / POWER(26,2) + 65)
+ CHAR ((@ID-1) % POWER(26,2) / 26 + 65)
+ CHAR ((@ID-1) % 26 + 65)
END
GO
IF OBJECT_ID('dbo.GetNextAlphaID','FN') IS NOT NULL
DROP FUNCTION dbo.GetNextAlphaID
GO
CREATE FUNCTION dbo.GetNextAlphaID ()
RETURNS char(5)
AS
BEGIN
DECLARE @MaxID char(5), @ReturnVal char(5)
SELECT @MaxID = MAX(AlphaID) FROM dbo.MyTable
IF @MaxID < 'ZZZZZ'
RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
IF @MaxID IS NULL
RETURN 'AAAAA'
SELECT @MaxID = MAX(AlphaID)
FROM dbo.MyTable
WHERE AlphaID < dbo.ConvertIntToAlphaID((SELECT COUNT(*) FROM dbo.MyTable))
IF @MaxID IS NULL
RETURN 'AAAAA'
RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
END
GO
SELECT * FROM dbo.MyTable ORDER BY dbo.ConvertAlphaIDToInt(AlphaID)
GO
SELECT dbo.GetNextAlphaID () AS 'NextAlphaID'
By the way, if you don't want to assume contiguity, you can do as you suggested and (if there's a 'ZZZZZ' row) use the first gap in the sequence. Replace the last function with this:
IF OBJECT_ID('dbo.GetNextAlphaID_2','FN') IS NOT NULL
DROP FUNCTION dbo.GetNextAlphaID_2
GO
CREATE FUNCTION dbo.GetNextAlphaID_2 ()
RETURNS char(5)
AS
BEGIN
DECLARE @MaxID char(5), @ReturnVal char(5)
SELECT @MaxID = MAX(AlphaID) FROM dbo.MyTable
IF @MaxID < 'ZZZZZ'
RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
IF @MaxID IS NULL
RETURN 'AAAAA'
SELECT TOP 1 @MaxID=M1.AlphaID
FROM dbo.Mytable M1
WHERE NOT EXISTS (SELECT 1 FROM dbo.MyTable M2
WHERE AlphaID = dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(M1.AlphaID) + 1 )
)
ORDER BY M1.AlphaID
IF @MaxID IS NULL
RETURN 'AAAAA'
RETURN dbo.ConvertIntToAlphaID(dbo.ConvertAlphaIDToInt(@MaxID)+1)
END
GO