SQL - Zahlenfolge erzeugen

Generating a number sequence

There's some use for a number sequence. For example a recordset with an index. If for a specific indes there's no entry, the index is not contained in the set. By joining the set to the full sequence of indices, absent entries show up as empty rows.

No loops

Loops of single statements are possible in SQL but expensive

Cartesian product

Das Cartesian product of two sets is the set of all pairs that can be arranged from one element of either set. Thus, the numbers 0 bis 99 are the Cartesian produkt of the sets (0...9) and (0, 10,...,90). The vector of the tens is the vector of ones, multiplied by 10.

Sample Code

DECLARE @p_N int = 123
DECLARE @p_N0 int = 0

WITH 
e1(n) AS (
	SELECT n FROM(
		VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))
		AS a(n)),                                          -- 10^1
e2(n) AS (SELECT 10*e1.n+b.n  FROM e1 CROSS JOIN e1 AS b), -- 10^2
e3(n) AS (SELECT e2.n+100*c.n FROM e2 CROSS JOIN e1 AS c)  -- 10^3
SELECT e3.n+@p_N0 AS n FROM e3 WHERE e3.n < @p_N