SS090901 - การค้นหาเลขที่หายไป

WITH TableInvoice(InvNo) AS ( 
     SELECT 'INV0096'
     UNION ALL
     SELECT 'INV0097'
     UNION ALL
     SELECT 'INV0099'
     UNION ALL
     SELECT 'INV0100'
     UNION ALL
     SELECT 'INV0102'
     UNION ALL
     SELECT 'INV0103'
    ),
    CTE AS ( 
     SELECT CAST(SUBSTRING(MIN(InvNo), 4, 4) AS INT) MinInvNo,
            CAST(SUBSTRING(MAX(InvNo), 4, 4) AS INT) MaxInvNo
     FROM TableInvoice
     UNION ALL
     SELECT MinInvNo + 1
        , MaxInvNo
     FROM CTE
     WHERE MinInvNo < MaxInvNo
    )
             
SELECT Formatted
FROM CTE
    CROSS APPLY ( SELECT 'INV' + RIGHT('0000'+ CAST(MinInvNo AS VARCHAR(4)),4) ) TableCommon ( Formatted )
WHERE NOT EXISTS( SELECT InvNo FROM TableInvoice WHERE InvNo = Formatted )
OPTION  ( MAXRECURSION 0 ) ;