MSSQL: Generate a 8 digit serial/Barcode number from a prefix number I have a complicated task ahead of me and I really could use some of your brains to help me out.

At my work we have a database where we store barcode digits. The table contains a 6 digit as a prefix for an 8 digit barcode, were we currently manually calculate the last 2 digits of the barcode in Excel.

Here are the steps of how we manually calculate the last 2 digits:

Barcode prefix example: 

declare @prefix int
set @prefix = 573071


1) Each prefix should be generated 10 times, where the 7th digit auto increments from 0 to 9

5730710
5730711
5730712 
5730713
5730714
5730715
5730716
5730717
5730718
5730719


2) After adding the 7th digit, we use the following formula to calculate the 8th digit for each row:


First we start calculating the first row((5730710). Then we begin with calculating the first, third, fifth, seventh digit and multiply by 3 and add them together

5730710:
5 x 3     
3 x 3     
7 x 3     
0 x 3 

Then we take the second, forth, sixth digits and mutiply by 1 and add them together:

7 x 1
0 x 1
1 x 1

We add both results using SUM (45 + 7 = 53)
Then we round up 53 to nearest 10 (upwards only) in this case it has to be 60 

DECLARE @num int = 53;
SELECT @num + (10 - (@num % 10)); = 60

After we round up we subtracts the round up value with the original value:
(60 - 53 = 7)


....And finally we have generated an 8 digit barcode from a 6 digit prefix: 
    Result: 57307107

So my question is, how do i do this through sql?

Here is how I am calculating the 8th digit:

declare @prefix int
set @prefix = 5730710
Select 
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
) 
+ 
(10 - ((
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)%10))

-
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)


But I'm still missing how to auto generate the 7th digit from 0 to 9 and put it into the above formula,
and lastly once the 7th and 8th digits are calculated I need them to be added to the prefix to finally
see the final result of 8 digit barcode.
DECLARE @prefix INT;
SET @prefix = 573071;
WITH 
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num &lt; 9)
SELECT num, 
@prefix*100+num*10+9-((@prefix/100000+(@prefix/1000)%10+(@prefix/10)%10+num)*3+((@prefix/10000)%10+(@prefix/100)%10+@prefix%10)-1)%10 barcode
FROM cte1;


fiddle

The option without variable declaration:

WITH 
cte0 AS (SELECT 573071 AS prefix),
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num &lt; 9)
SELECT num, 
prefix*100+num*10+9-((prefix/100000+(prefix/1000)%10+(prefix/10)%10+num)*3+((prefix/10000)%10+(prefix/100)%10+prefix%10)-1)%10 barcode
FROM cte0, cte1;