Sql-server – 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.