SQL select numbers - dynamically with incrementing value in each row

Question

Is there a way to write a dynamic sql select statement (not query a table/column) that will return something like this

id
1
2
3
4
5
... 
etc.

I need to write several generic queries that pull values 1 through X
(X will be pre-determined by a different query prior).

Say X is 5 the data will return 5 rows with 1 2 3 4 5
if X is 27 the data will return 1 2 3 ... 27 and so on.

on additional comment this is used in a third party sybase software and i am limited to the complexity of queries it seems creating and dropping tables doesn't seem to work

in comments you've mentioned `third party sybase software` and `complexity of temp tables`; you may want to update your question with additional details as to your environment (eg, are you writing SQL Server T-SQL code that will be run on a remote Sybase ASE database, etc), to include various product names and versions; the more details we have the better ... otherwise we could waste time posting answers that won't be acceptable/doable in your environment — markp-fuso, Nov 27 '17 at 19:38
I'd also suggest you provide a more detailed example of how you plan to use those numbers; as currently worded, I can't tell if you want to use the numbers for inserts, deletes, updates, selects ... if you want to use them for join/SARG purposes ... if a `select`, why you can't do something like `select * from some_table where id between 1 and X` — markp-fuso, Nov 27 '17 at 19:43
This question indicates to me that `SArg` isn't going to be a term recognised by the Op. — MatBailie, Nov 27 '17 at 20:01
one other question ... what are you expecting as the largest value for X? — markp-fuso, Nov 27 '17 at 20:44

score 2 · Answer 1 · answered Nov 27 '17 at 17:58

2

Sure you can use a tally table. http://www.sqlservercentral.com/articles/T-SQL/62867/

Or maybe all you need is ROW_NUMBER. https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

answered Nov 27 '17 at 17:58

Sean Lange

33,028
3
25
40

i couldn't use things this complex the data is going to third party sybse software and i can work with basic simple queries that is hwy i'm having a hard time (although thank you for the tip, i learned something new) – Elizabeth Nov 27 '17 at 18:19
A tally table isn't complex. It's by far the cleanest and simplest solution. Create a table of numbers from 0 to and then use joins. For example 0..86399 gives one entry for every second of the day. or 0..1023 gives a table you can join against itself repeatedly to get useful and arbitrarily large ranges of values... `SELECT * FROM dbo.numbers WHERE id BETWEEN 1 AND 27` is as simple as you'll ever get... – MatBailie Nov 27 '17 at 18:27

score 0 · Answer 2 · answered Nov 27 '17 at 18:00

0

You could try a recursive CTE like so

declare @mx integer=4

;with test as (
select 1 as id
union all
select id+1
from test
where id<@mx
)
select * from test

answered Nov 27 '17 at 18:00

Luke Miller

36
4

Might want to include `MAXRECURSION(0)` to prevent bumping into weird behaviour above 100. And then explain the costs/issues that exist when you reach 32k and above... – MatBailie Nov 27 '17 at 18:36
1

Do be careful here...a cte used for counting is basically a loop. http://www.sqlservercentral.com/articles/T-SQL/74118/ – Sean Lange Nov 27 '17 at 19:14

Hunter Nelson · Answer 3 · 2017-11-27T18:13:37.707

0

27 selects:

DECLARE @Count int = 1
DECLARE @MaxCount int = 27 -- Whatever your incoming max is

WHILE @Count <= @MaxCount
BEGIN
SELECT @Count

SELECT @Count = @Count + 1
END

One select:

CREATE TABLE #TEMPNUMS (AUTONUM INT IDENTITY (1,1), GHOSTVAL VARCHAR(1))


DECLARE @Count int = 1
DECLARE @MaxCount int = 27 -- Whatever your incoming max is

WHILE @Count <= @MaxCount
BEGIN
INSERT INTO #TEMPNUMS (GHOSTVAL)
SELECT ''

SELECT @Count = @Count + 1
END

SELECT AUTONUM FROM #TEMPNUMS

DROP TABLE #TEMPNUMS

edited Nov 27 '17 at 18:13

answered Nov 27 '17 at 18:06

Hunter Nelson

1,707
3
20
37

I can't tell if you want 27 single selects or one select with 27 rows. Both answers included. – Hunter Nelson Nov 27 '17 at 18:13
1 select with 27 rows. – Elizabeth Nov 27 '17 at 18:13
Updated answer with one select. – Hunter Nelson Nov 27 '17 at 18:14
this will be in a third party application and i have to confirm if the complexity of temp tables will be supported. – Elizabeth Nov 27 '17 at 18:17
You should still upvote answer's that answer your original question, even if you find new information that makes them invalid :) – Hunter Nelson Nov 27 '17 at 18:27
Added another answer that accommodates the new information provided. – Hunter Nelson Nov 27 '17 at 18:30

score 0 · Answer 4 · answered Nov 27 '17 at 18:13

0

Have you tried to use CTE like this?

WITH OrderedRows (ROWN)
AS (
    SELECT 1 AS ROWN

    UNION ALL

    SELECT r.ROWN + 1 AS ROWN
    FROM OrderedRows r
    WHERE r.ROWN < 100
    )
SELECT *
FROM OrderedRows

answered Nov 27 '17 at 18:13

Rogério Carvalho Batista

462
2
7

Might want to include `MAXRECURSION(0)` to prevent bumping into weird behaviour above 100. And then explain the costs/issues that exist when you reach 32k and above... – MatBailie Nov 27 '17 at 18:30
Also, it's better if you start with two values (0 and 1) and then start joining the table against itself *(each level of recursion doubling the range you're working with, giving much higher ranges and much better scaling)*. – MatBailie Nov 27 '17 at 18:32
1

Do be careful here...a cte used for counting is basically a loop. http://www.sqlservercentral.com/articles/T-SQL/74118/ – Sean Lange Nov 27 '17 at 19:14

score 0 · Answer 5 · answered Nov 27 '17 at 18:29

Assuming your max number isn't ever going to be greater than 9999. If it is then you will need to restructure the query to add another layer.

DECLARE @MaxCount int = 27 -- Whatever your incoming max is

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
     where ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n between 1 and @MaxCount
ORDER BY 1

See this answer on another similar question: How to generate a range of numbers between two numbers?

Just to avoid repeating yourself, you could have something like `WITH ten AS SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n)) SELECT blah FROM ten ones CROSS JOIN ten tens CROSS JOIN ten hundreds blahblahblah...` — MatBailie, Nov 27 '17 at 18:35

SQL select numbers - dynamically with incrementing value in each row

5 Answers5