how to generate a sequence of numbers?

[jeremysimmons]

I certainly cannot do this:
select 1 UNION ALL
select 2 UNION ALL
select 3.

Syntax error at or about (statement excerpt). (10713)
SQL-92 found a sql syntax error in the sql statement sent to it by the client. "statement excerpt" is the portion of the statement where the syntax error was found. The beginning of the excerpt is where SQL-92 detected the syntax error.

I also cannot do this

select 1 FROM table_with_no_rows UNION ALL
select 2 FROM table_with_no_rows

because I get back 1 row.

What's the solution for this?

 

[TomBascom]

That depends. What are you trying to do?

 

[jeremysimmons]

tom
My immediate goal is to generate a single result set using SQL that contains a sequence of numbers in a specific range.
A sequence of numbers helps with all kinds of things in SQL. Parsing strings, generating dates, replacing complex procedural constructs and cursors with simple and efficient set based code.
examples:
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx
http://sqlserver2000.databases.aspf...onsider-using-an-auxiliary-numbers-table.html

In my very specific case I need a a dynamic date dimension table for a data-warehouse problem. This is further constrained by the inability to modify the schema by creating tables.