Q:

How to concatenate string of consecutive numbers (in a range) in SQL with a loop?

This question may have been answered, but, I might have a different problem to solve.
I have the following string: '123456' and the following range: 05 - 10
How can I concatenate the string with each number in the range, so I have:
12345605
12345606
12345607
12345608
12345609
12345610
I will have a stored procedure that accepts 6 digits code, min and max numbers in the range. Then I will have to do some kind of processing of the data. It can be inserted later on or deleted or any sort of things.
This is what I will have in my stored procedure:
declare
    @firstSix varchar(6),
    @min varchar(2),
    @max varchar(2)

To do that I have started to write a loop:

WHILE(CAST(@min AS int) <= CAST(@max AS int)
BEGIN
     --here I will need to write a logic that concatenates the string. 
     SET @min = @min + 1
     SELECT @firstSix
END

So, using the following input 
exec myProcedure '123456', '05', '10' 
I will have, the following output:
12345605
12345606
12345607
12345608
12345609
12345610

or using the following input 
exec myProcedure '123456', '15', '20' 
I will have the following output: 
12345615
12345616
12345617
12345618
12345619
12345620

I will, probably need to have a logic to determine if a value in the range has a leading zero or something
What is the best approach here?

A:

You want recursive CTE :
with cte as (
     select @firstSix as firstSix , cast(@min as int) as mn, cast(@max as int) as mx
     union all
     select firstSix, mn + 1 , mx
     from cte
     where mn < mx
)
select concat(firstSix, right(concat('0', mn), 2))
from cte c;

By default is has 100 recursion level if you have a large suffix then query hint option (maxrecursion 0).
EDIT : Using LOOP 
DECLARE @table table (firstSix varchar(255))

WHILE(CAST(@min AS int) <= CAST(@max AS int))
BEGIN
     --here I will need to write a logic that concatenates the string. 
     INSERT INTO @table (firstSix)
         SELECT concat(@firstSix, right(concat('0', @min), 2))

     SET @min = @min + 1
END

SELECT firstSix
FROM @table;

