I have two numbers as input from the user, like for example 1000
and 1050
.
How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:
1000
1001
1002
1003
.
.
1050
I have two numbers as input from the user, like for example 1000
and 1050
.
How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:
1000
1001
1002
1003
.
.
1050
It work for me !
If you don't have a problem installing a CLR assembly in your server a good option is writing a table valued function in .NET. That way you can use a simple syntax, making it easy to join with other queries and as a bonus won't waste memory because the result is streamed.
Create a project containing the following class:
Put the assembly somewhere on the server and run:
Now you can run:
Nothing new but I rewrote Brian Pressler solution to be easier on the eye, it might be useful to someone (even if it's just future me):
Here's what I came up with:
Generates up to 2^24 values. Join conditions keep it fast for small values.
This only works for sequences as long as some application table has rows. Assume I want sequence from 1..100, and have application table dbo.foo with column (of numeric or string type) foo.bar:
Despite its presence in an order by clause, dbo.foo.bar does not have to have distinct or even non-null values.
Of course, SQL Server 2012 has sequence objects, so there's a natural solution in that product.
The best speed when run query