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
+

DECLARE @prefix INT;
SET @prefix = 573071;
WITH 
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num < 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 < 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;

StackExchange Database Administrators Q#239951, answer score: 6