Nice, pulling out the table is a big improvement. In looking at that, I was thinking about doing more than just adding one. So I created an addition function that allows upto 128 digit numbers in base 36, and then a separate function to constrain it to ZZ max value for Tickets.
Your solution still beats mine (4ms on my test machine verses 9ms for my solution).
Function for base36 addition
ALTER FUNCTION dbo.Base36Add( @value1 varchar(128), @value2 varchar(128) ) RETURNS varchar(128) AS
BEGIN
DECLARE @digits char(36); SET @digits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @sum varchar(16), @i int, @s-2 int, @d1 int, @d2 int, @r int;
-- 1. null values are invalid -------------------------------------------------
IF @value1 IS NULL OR @value2 IS NULL
GOTO Exit_Error;
-- 2. pad so they are the same length -----------------------------------------
IF len(@value1) < len(@value2)
SET @value1 = replicate( substring(@digits,1,1), len(@value2)-len(@value1) ) + @value1;
ELSE IF len(@value2) < len(@value1)
SET @value2 = replicate( substring(@digits,1,1), len(@value1)-len(@value2) ) + @value2;
-- 3. sum and carry -----------------------------------------------------------
SELECT
@sum = '', /* init string for prepending */
@i = len(@value1), /* start at least signifigant place */
@r = 0; /* start with no remainder */
WHILE 0 < @i BEGIN /* for each place */
-- a. convert the sum of the remainder plus the current place in both values to an integer
SELECT
@d1 = charindex( substring(@value1,@i,1), @digits ) - 1,
@d2 = charindex( substring(@value2,@i,1), @digits ) - 1;
IF (coalesce(@d1,-1) = -1 OR coalesce(@d2,-1) = -1)
GOTO Exit_Error;
-- b. find the resulting total for this place
SET @sum = substring(@digits, (@s % 36) + 1, 1)
+ @sum;
-- c. find the remainder to carry forward
SET @r = (@s / 36);
-- d. next place...
SET @i = @i - 1;
END
-- 4. final remainder?
IF 0 < @r BEGIN
IF len(@sum) < 128 /* defined return value size */
SET @sum = substring(@digits, @r + 1, 1) + @sum;
ELSE
SET @sum = '**'; /* overflow */
END
Exit_Success:
RETURN @sum;
Exit_Error:
RETURN '--'; /* invalid input */
END
Function to constrict Ticket numbers to 00 through ZZ (0 to 1296)
CREATE FUNCTION [dbo].[NextTicket]( @Ticket varchar(2) ) RETURNS varchar(2) AS
BEGIN
IF @Ticket = 'ZZ'
RETURN '**';
RETURN dbo.Base36Add( @Ticket, '1' );
END
Test queries for timing
SELECT
dbo.NextTicket('00') AS Expect_01,
dbo.NextTicket('0Y') AS Expect_0Z,
dbo.NextTicket('0Z') AS Expect_10,
dbo.NextTicket('ZY') AS Expect_ZZ,
dbo.NextTicket('ZZ') AS Expect_Overflow,
dbo.NextTicket(' Z') AS Expect_Invalid_01,
dbo.NextTicket('@0') AS Expect_Invalid_02,
dbo.NextTicket('A$') AS Expect_Invalid_03,
dbo.NextTicket(NULL) AS Expect_Invalid_04
SELECT
dbo.fn_NextSerial('00') AS Expect_01,
dbo.fn_NextSerial('0Y') AS Expect_0Z,
dbo.fn_NextSerial('0Z') AS Expect_10,
dbo.fn_NextSerial('ZY') AS Expect_ZZ,
dbo.fn_NextSerial('ZZ') AS Expect_Overflow,
dbo.fn_NextSerial(' Z') AS Expect_Invalid_01,
dbo.fn_NextSerial('@0') AS Expect_Invalid_02,
dbo.fn_NextSerial('A$') AS Expect_Invalid_03,
dbo.fn_NextSerial(NULL) AS Expect_Invalid_04