Sometimes, you need to show range of numbers or may be asked to generate a range of numbers between two numbers in SQL Server.
Well, it is pretty simple and can be done using different-different approaches, but one of the most common and easiest way of doing it is using a Recursive CTE.
Lets create a query that uses a recursive CTE and returns a range of numbers between two numbers.
Following SQL query returns the numbers between two specified numbers that is 1 and 10.
DECLARE @RangeStartFrom INT= 1 DECLARE @RangeEndTo INT= 10 ; ; WITH GenerateNumbers AS ( SELECT @RangeStartFrom AS Number UNION ALL SELECT Number +1 FROM GenerateNumbers WHERE Number +1<= @RangeEndTo ) SELECT * FROM GenerateNumbers option (MAXRECURSION 0)
So, when you execute the above query, it will return the numbers from 1 to 10.
Lets execute the query and see the output.
As you can see, it returns numbers starting from 1 to 10.
Also you can convert this query into stored procedure that will take two input parameters for range and returns the ranges of numbers based on those parameter values.
Lets create a stored procedure as shown below.
CREATE PROC GenerateNumbers @RangeStartFrom AS INT = 0, @RangeEndTo AS INT =0 AS BEGIN ; WITH GenerateNumbers AS ( SELECT @RangeStartFrom AS Number UNION ALL SELECT Number +1 FROM GenerateNumbers WHERE Number +1<= @RangeEndTo ) SELECT * FROM GenerateNumbers option (MAXRECURSION 0) END
Lets execute the stored procedure, this time we we generate the range of numbers between 20 to 30.