sequence generation in sql server

        @pattern				NVARCHAR(10),--AANNNN,AAANNN,AANNNN
	@last_sequence			NVARCHAR(10),--AA00001,AAA0001,AA00001
	@last_sequence_order	INT                        -- 0

CREATE TABLE #temp
	(
		next_sequence_character	NVARCHAR(10),
		next_sequence_number NVARCHAR(10),
		next_sequence_order	INT
	)

	/*Table contains generated sequence number and sequence order*/
	CREATE TABLE #sequence	
	(
		next_sequence_no	NVARCHAR(10),
		next_sequence_order	INT
	)

	DECLARE 
		@ct INT,
		@pattern_character_count INT,
		@pattern_numeric_count INT,
		@pattern_character_position INT,
		@pattern_numeric_position INT,
		@final_string nvarchar(max)
		
		SELECT	@pattern_character_count = LEN(@pattern)-LEN(replace(@pattern,'A','')),-- Count of characters in the given pattern
				@pattern_numeric_count = LEN(@pattern)-LEN(replace(@pattern,'N','')),-- Count of numbers in the given pattern
				@pattern_character_position =  PATINDEX('%A%',@pattern),-- Get the starting position of character in the given pattern
				@pattern_numeric_position =  PATINDEX('%N%',@pattern),-- Get the starting position of numbers in the given pattern
				@ct = @pattern_character_count 

	DECLARE 
		@character NVARCHAR(10), 
		@number NVARCHAR(10),
		@final_character NVARCHAR(10),
		@number_character NVARCHAR(MAX),
		@final_sequence NVARCHAR(10),
		@number_to_compare NVARCHAR(25)

		SELECT	@character = SUBSTRING(@last_sequence,@pattern_character_position,@pattern_character_count),-- Get the characters in the provided last sequence
				@number =SUBSTRING(@last_sequence,@pattern_numeric_position,@pattern_numeric_count),-- Get the numbers in the provided last sequence
				@number_to_compare = REPLICATE('9',@pattern_numeric_count)

		SET @last_sequence = ISNULL(@last_sequence,'0')

		PRINT @last_sequence

		IF @last_sequence != '0'
		BEGIN		
			SET @final_string =' INSERT INTO #temp (next_sequence_character,next_sequence_number,next_sequence_order) SELECT '

			IF @number = @number_to_compare
			BEGIN
				SET  @last_sequence_order = @last_sequence_order + 1
			
				WHILE(@ct>=1)
				BEGIN
					SELECT @final_string =	@final_string + ' CHAR(65+('+CAST(@last_sequence_order AS NVARCHAR)+'%POWER(26,'
											+CAST(@ct AS NVARCHAR)+'))/POWER(26,'+CAST(@ct AS NVARCHAR)+'-1)) + ';
					SET @ct = @ct - 1
				END
				SELECT	@number_character =	' + RIGHT(REPLICATE(''0'','+CAST(@pattern_numeric_count AS NVARCHAR)+') + LTRIM(STR(1)),'
											+ CAST(@pattern_numeric_count AS NVARCHAR)+')'
			END
			ELSE
			BEGIN
				WHILE(@ct>=1)
				BEGIN
					SELECT @final_string =	@final_string + ' CHAR(65+('+CAST(@last_sequence_order AS NVARCHAR)+'%POWER(26,'
											+ CAST(@ct AS NVARCHAR)+'))/POWER(26,'+CAST(@ct AS NVARCHAR)+'-1)) + ';

					SET @ct = @ct - 1 
				END	
				SELECT	@number_character =  ' + RIGHT(REPLICATE(''0'','+CAST(@pattern_numeric_count AS NVARCHAR)+') 
				+ LTRIM(STR('+ CAST(@number AS NVARCHAR)+'+1)),'+CAST(@pattern_numeric_count AS NVARCHAR)+') '
			END		
				
		END
		ELSE
		BEGIN

			SET @last_sequence_order = 0
				
			SET @final_string =' INSERT INTO #temp (next_sequence_character,next_sequence_number,next_sequence_order) SELECT '

			WHILE(@ct>=1)
			BEGIN
				SELECT @final_string =	@final_string + ' CHAR(65+('+CAST(@last_sequence_order AS NVARCHAR)+'%POWER(26,'
										+CAST(@ct AS NVARCHAR)+'))/POWER(26,'+CAST(@ct AS NVARCHAR)+'-1)) + ';
				SET @ct = @ct - 1
			END
			SELECT	@number_character =	' + RIGHT(REPLICATE(''0'','+CAST(@pattern_numeric_count AS NVARCHAR)+') + LTRIM(STR(1)),'
										+ CAST(@pattern_numeric_count AS NVARCHAR)+')'
		END	

		SET @final_string = SUBSTRING(@final_string,1,LEN(@final_string)-1)
		SET @final_string = @final_string +','+  @number_character + ',' + CAST(@last_sequence_order AS NVARCHAR)

		EXECUTE SP_EXECUTESQL @final_string

		DECLARE @seq_chara nvarchar(10),@seq_number nvarchar(3),@seq nvarchar(10)
		SELECT @seq_chara = next_sequence_character,@seq_number = next_sequence_number FROM #temp

		DECLARE @final NVARCHAR(10)

		SET @final  = REPLACE(@pattern,REPLICATE('A',@pattern_character_count),@seq_chara)		
		SET @final  = REPLACE(@final,REPLICATE('N',@pattern_numeric_count),@seq_number)

		INSERT INTO #sequence (next_sequence_no,next_sequence_order) VALUES (@final,@last_sequence_order)

		SELECT next_sequence_no,next_sequence_order FROM #sequence

		DROP TABLE #sequence
		DROP TABLE #temp