T-SQL procedure to increment an alpha character

USE [MARIAS] GO /****** Object: StoredProcedure [dbo].[prEndorsementSuffixGet] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Sherry Lake -- Create date: 09/04/2009 -- Description: Retrieves all endorsements with suffixes and -- parses the endorsement number and suffix -- used to determine next appropriate suffix for Endorsement Transfers -- ============================================= CREATE PROCEDURE [dbo].[prEndorsementSuffixGet] @EndorsementNumber nvarchar(15) ,@EndorsementType nvarchar(10) ,@NewSuffix nvarchar(5) OUTPUT AS SET NOCOUNT ON DECLARE @ProgramName nvarchar(200) ,@DerSuffix nvarchar(5) ,@MaxSuffix nvarchar(5) ,@asciiValue int ,@DerEndNum nvarchar(15) ,@SuffixExists int SET @ProgramName = 'prEndorsementSuffixGet' SET @SuffixExists = ISNUMERIC(substring(@EndorsementNumber, LEN(@EndorsementNumber)-0, LEN(@EndorsementNumber))) BEGIN --if there is already a suffix on the endorsement number IF @SuffixExists = 0 BEGIN set @DerSuffix = substring(@EndorsementNumber, LEN(@EndorsementNumber)-0, LEN(@EndorsementNumber)) set @DerEndNum = substring(@EndorsementNumber, LEN(@EndorsementNumber)-LEN(@EndorsementNumber), LEN(@EndorsementNumber)) END --if not already an endorsement number ELSE IF NOT @SuffixExists = 0 BEGIN set @DerEndNum = @EndorsementNumber set @DerSuffix = NULL END --get the max suffix already used for this endorsement number in the database select @MaxSuffix = MAX(v.DerSuffix) from ( select distinct e.FK_EndorsementType, e.EndorsementNumber, substring(EndorsementNumber, LEN(EndorsementNumber)-0, LEN (EndorsementNumber)) as DerSuffix, substring(EndorsementNumber, LEN(EndorsementNumber)-LEN(EndorsementNumber), LEN(EndorsementNumber)) as DerEndNum from dbo.Endorsement e where EndorsementNumber is not null and ISNUMERIC(substring(EndorsementNumber, LEN(EndorsementNumber)-0, LEN(EndorsementNumber))) = 0 ) v where v.DerEndNum = @DerEndNum and v.FK_EndorsementType = @EndorsementType --if there are no suffixes used on this endorsement number in the DB, assign suffix A if @MaxSuffix is not null BEGIN set @asciiValue = ASCII(@MaxSuffix) +1 --increment the suffix after converting to ASCII number set @NewSuffix = CHAR(@asciiValue) --cast incremented ASCII number back to char suffix END else BEGIN set @NewSuffix = 'A' END END GO