MSSQL: Generate a 8 digit serial/Barcode number from a prefix number
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;