I am working a SQL Server stored procedure to generate a 10 digits sequence with the following patterns where [CustomerCode] is say 'ABC':
[CustomerCode]0000001
...
[CustomerCode]9999999
[CustomerCode]A000001
...
[CustomerCode]Z999999
[CustomerCode]ZA00001
...
[CustomerCode]ZZ99999
[CustomerCode]ZZA0001
...
[CustomerCode]ZZZZZZZ
So this stored procedure will take a string as input parameter, and return the next value e.g it takes 'ABCZ999999' and returns 'ABCZA00001'.
I have the following stored procedure and functions but they do not work fully and I can't seem to get it right.
Any help is appreciated.
-- Function to increment numeric value
CREATE FUNCTION [dbo].[IncrementNumericValue]
(@CurrentValue Varchar(2))
RETURNS Varchar(2)
AS
BEGIN
DECLARE @RetValue Varchar(2)
IF (@CurrentValue < CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST((CAST(@CurrentValue AS Int) + Cast(1 AS Int)) AS Varchar(2))
END
ELSE IF (@CurrentValue >= CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST(0 AS Varchar(2))
END
ELSE
BEGIN
SET @RetValue = CAST('-' AS Varchar(2))
END
RETURN (@RetValue)
END
-- Function to increment alpha value
CREATE FUNCTION [dbo].[IncrementAlphaValue]
(@CurrentValue Varchar(2))
RETURNS Varchar(2)
AS
BEGIN
DECLARE @RetValue Varchar(2)
IF (@CurrentValue < CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST((CAST(@CurrentValue AS Int) + CAST(1 AS Int)) AS Varchar(2))
END
ELSE IF (@CurrentValue = CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST('A' As Varchar(2))
END
ELSE IF ((@CurrentValue >= CAST('A' AS Varchar(2)))
AND (@CurrentValue < CAST('Z' AS Varchar(2))))
BEGIN
SET @RetValue = CAST((Char((CAST(ASCII(@CurrentValue) AS Int) + CAST(1 AS Int)))) AS Varchar(2))
END
ELSE IF (@CurrentValue = CAST('Z' AS Varchar(2)))
BEGIN
SET @RetValue = CAST(0 AS Varchar(2))
END
ELSE
BEGIN
SET @RetValue = CAST('-' AS Varchar(2))
END
RETURN (@RetValue)
END
-- Function to calculate the next sequence of the pattern
CREATE PROCEDURE [dbo].[GetNextSequence]
@NewTarget Varchar(10) OUTPUT
AS
BEGIN
DECLARE @LastVariable Varchar(10)
DECLARE @PositionIndex Int
DECLARE @PassParameterWidth Int
DECLARE @TargetWidth Int
DECLARE @SelectedValue Varchar(2)
DECLARE @NewValue Varchar(2)
DECLARE @FinalValue Varchar(10)
DECLARE @ReplaceStringLength Int
DECLARE @ReplaceString Varchar(10)
DECLARE @NewCODE Varchar(10)
DECLARE @MaxTargetWidth Int
DECLARE @customerCode char(3)
DECLARE @NumberOfNine Int
DECLARE @AlphaNumericPartWidth Int
BEGIN
BEGIN
-- Input
SET @customerCode = 'ABC'
SELECT @LastVariable = 'ABCZ999999'
SET @PassParameterWidth = Len(@LastVariable)
SET @AlphaNumericPartWidth = @PassParameterWidth - Len(@customerCode)
SET @LastVariable = SUBSTRING(@LastVariable, 4, 7)
SET @TargetWidth = Cast(4 AS Int)
SET @MaxTargetWidth = Cast(7 AS Int)
SET @NewValue = ''
SET @FinalValue = ''
SET @ReplaceStringLength = Cast(0 AS Int)
SET @ReplaceString = ''
SET @NumberOfNine = 0
IF (@AlphaNumericPartWidth = CAST(7 AS Int))
BEGIN
SET @PositionIndex = @AlphaNumericPartWidth
WHILE(@PositionIndex > 0)
BEGIN
SET @SelectedValue = SUBSTRING(@LastVariable, @PositionIndex, 1)
IF (@SelectedValue LIKE '[A-Z]') -- Increment alpha
SET @NewValue = dbo.IncrementAlphaValue(@SelectedValue)
ELSE -- Increment numeric
SET @NewValue = dbo.IncrementNumericValue(@SelectedValue)
-- Right most digit
IF (@PositionIndex = 1)
BEGIN
IF (@SelectedValue = 'Z')
BEGIN
-- If left of 'Z' all '9' replace with 'ZA'
IF (LEN(REPLACE(PARSENAME(REPLACE(@LastVariable, 'Z', '.'), 1), '9','')) = 0)
BEGIN
SET @LastVariable = REPLACE(@LastVariable, 'Z', 'ZA');
SET @LastVariable = REPLACE(@LastVariable, '9', '0');
RETURN
END
END
-- If prefixed with letter or All '9', increment alpha
IF (@NewValue LIKE '[A-Y]' OR LEN(REPLACE(@LastVariable, '9','')) = 0)
BEGIN
SET @NewValue = dbo.IncrementAlphaValue(@SelectedValue)
END
END
SET @FinalValue = @NewValue + @FinalValue
-- Break is it's 1
IF (@NewValue <> 9)
BREAK
-- Move position to left
SET @PositionIndex = @PositionIndex - Cast(1 As Int)
END
-- Check Final value length
IF (LEN(@FinalValue) <= @MaxTargetWidth)
BEGIN
SET @LastVariable = @customerCode + @LastVariable
-- Get replace string length
SET @ReplaceStringLength = @PassParameterWidth - Len(@FinalValue)
-- Get replace string
SET @ReplaceString = LEFT(@LastVariable, @ReplaceStringLength)
-- NEW CODE
SET @NewCODE = @ReplaceString + @FinalValue
SET @NewTarget = @NewCODE
END
ELSE
BEGIN
SET @NewTarget = 'ERROR1'
END
END
ELSE
BEGIN
SET @NewTarget = 'ERROR222'
END
END
END
END