Generate number between Range in TSQL using Recursive CTE

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 357
Suppose a range has been provided as 4-12 and we have to generate the number between 4 and 12.

The below TSQL Script which use a Recursive CTE will help to do so

DECLARE @range VARCHAR(10) = '4-12'
DECLARE @startRange INT = PARSENAME(REPLACE(@range, '-', '.'), 2)
DECLARE @lastRange INT = PARSENAME(REPLACE(@range, '-', '.'), 1)


;WITH numCTE AS(
SELECT
Number=@startRange
UNION ALL

SELECT Number=Number+1
FROM numCTE WHERE Number<@lastRange)

SELECT
Number
FROM numCTE
OPTION (MAXRECURSION 0)


Result
---------
Number
4
5
6
7
8
9
10
11
12

Comments or Responses

Login to post response