2014-12-06

SQL Server Script to Print Sequential Numbers 


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- PRINT OUT LIST OF SEQUENTIAL NUMBERS FROM num_from TO num_to. Max set to up to 9999
DECLARE @num_from int, @num_to int
SELECT @num_from = 1000, @num_to = 1200

SELECT *
FROM (
  SELECT SEQ 
  = 1 * X1.N -- singles
  + 10 * X10.N -- tens
  + 100 * X100.N -- hundreds
  + 1000 * X1000.N -- thousands
  FROM (
    SELECT 0 AS N UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS X1
  CROSS JOIN (
    SELECT 0 AS N UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS X10
  CROSS JOIN (
    SELECT 0 AS N UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS X100
  CROSS JOIN (
    SELECT 0 AS N UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS X1000
) AS X
WHERE SEQ BETWEEN @num_from AND @num_to
ORDER BY 1
By at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)