SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE COP (
A001 varchar(40) NULL ,
A002 varchar(40) NULL ,
A003 varchar(40) NULL);

INSERT INTO COP (A001,A002,A003)
VALUES 
('1AA','1111',NULL),
('1AA','1112',''),
('2AA','1111',''),
('2AA','1112','XX55555555'),
('3AA','1110','');

SELECT *
FROM COP
ORDER BY A001,A002;


A001
A002
A003


1AA
1111
null

1AA
1112


2AA
1111


2AA
1112
XX55555555

3AA
1110



CREATE TABLE INV (
B001 varchar(40) NULL);

INSERT INTO INV (B001)
VALUES 
('AB00000000');

SELECT *
FROM INV;


B001


AB00000000


-- 以 INV B001 為發票起始值 ,對 COP 在 A003 是空的或是 null "試算發票號碼"
SELECT A001,A002,
(SELECT LEFT(B001,2) FROM INV)+RIGHT('00000000'+CONVERT(VARCHAR(8),
(SELECT CONVERT(INT,SUBSTRING(B001,3,8)) FROM INV)+
CONVERT(VARCHAR(8),ROW_NUMBER() OVER (ORDER BY A001,A002)-1)),8) AS A003NEW
FROM COP
WHERE ISNULL(A003,'')=''


A001
A002
A003NEW


1AA
1111
AB00000000

1AA
1112
AB00000001

2AA
1111
AB00000002

3AA
1110
AB00000003


-- 只對 COP A003 是空的或是 null 準備填入"試算發票號碼"
SELECT *
FROM COP AS A
LEFT JOIN (
-- 以 INV B001 為發票起始值 ,對 COP 在 A003 是空的或是 null "試算發票號碼"
SELECT A001,A002,
(SELECT LEFT(B001,2) FROM INV)+RIGHT('00000000'+CONVERT(VARCHAR(8),
(SELECT CONVERT(INT,SUBSTRING(B001,3,8)) FROM INV)+
CONVERT(VARCHAR(8),ROW_NUMBER() OVER (ORDER BY A001,A002)-1)),8) AS A003NEW
FROM COP
WHERE ISNULL(A003,'')=''
--
) AS B ON B.A001=A.A001 AND B.A002=A.A002
WHERE ISNULL(A.A003,'')='';


A001
A002
A003
A001
A002
A003NEW


1AA
1111
null
1AA
1111
AB00000000

1AA
1112

1AA
1112
AB00000001

2AA
1111

2AA
1111
AB00000002

3AA
1110

3AA
1110
AB00000003

A001	A002	A003NEW
1AA	1111	AB00000000
1AA	1112	AB00000001
2AA	1111	AB00000002
3AA	1110	AB00000003

A001	A002	A003	A001	A002	A003NEW
1AA	1111	null	1AA	1111	AB00000000
1AA	1112		1AA	1112	AB00000001
2AA	1111		2AA	1111	AB00000002
3AA	1110		3AA	1110	AB00000003