sequence generation in sql server

Emil
Posted by Emil under Sql Server category on | Points: 40 | Views : 933
Generate sequence depending upon the pattern

        @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

Comments or Responses

Login to post response