Generate number between Range in TSQL using master..spt_values

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 191
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)

SELECT DISTINCT Number = number
FROM master..spt_values
WHERE number BETWEEN @startRange AND @lastRange

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

Comments or Responses

Login to post response