Trying to generate a range of numbers excluding certain numbers (with outer query identifier)

Question

There have been quite a number of question regarding ranges in sql however i cant find anything that resembles my use case (most refer to a Tally or numbers table or procedures, something im trying to avoid seeing the elegance in the link below which uses neither)

Im trying to generate numbers between 1 and x, x coming for another table whilst excluding certain numbers from yet another table. (although there is a link between the 2, see the join)

Based on the answer for generating a range of numbers located at https://stackoverflow.com/a/64151448/1161646, im trying to do the following (approximation of the problem not the actual query):

select number
from excluding_numbers as exclusions join ranges range on range.id = exclusions.ranges_id,
     (Select 0 + ROW_NUMBER() over (order by (Select null)) as number
      from string_split(replicate(' ', range.limit - 1), ' ')) as numbers
where exclusions.number != number
  and range.id = 1

The problem is the range.limit in the inner query 'numbers'

Example data:

ranges

id	limit
1	120000
2	10
3	10000000000

excluding_numbers

id	number	ranges_id
1	1	2
2	2	2
3	50000	3

This is saying from the range of 1 to 10 (ranges id 2) exclude number 1 and 2, from ranges 1 to 10000000000 (ranges id 3) only exclude number 50000.

Answer 1

Assuming I understand correctly, and that your numbers are 1+, you could use a Tally table to do this. An inline method would be one method:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (SELECT x FROM dbo.YourTable) --Table with the number of rows you want. I assume this has one row
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7) --Up to 10,000,000 rows
SELECT T.I
FROM Tally T
WHERE NOT EXISTS (SELECT 1
                    FROM dbo.ExcludeTable ET--Table with numbers to exclude
                    WHERE ET.Number = T.I);

You could, however, create an inline table function for your tally, and then reference that instead:

CREATE FUNCTION [dbo].[Tally] (@LastNumber bigint, @Zero bit) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @Zero = 0
          AND @LastNumber IS NOT NULL --Return 0 rows if NULL
        UNION ALL
        SELECT TOP (ISNULL(@LastNumber,0)) --Return 0 rows if NULL
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7) --Up to 10,000,000 rows
    SELECT I
    FROM Tally T;
GO

SELECT T.I
FROM dbo.YourTable YT --Table with the number of rows you want. I assume this has one row
     CROSS APPLY dbo.Tally(YT.x,1) T
WHERE NOT EXISTS (SELECT 1
                    FROM dbo.ExcludeTable ET--Table with numbers to exclude
                    WHERE ET.Number = T.I);