Mar 15, 2014

Sql query to get the series of integer numbers of given ranges in sql server


Sql query to get the series or sequence of integer natural numbers of given ranges in sql server. 

For this I have created table valued function. This is as follow:

CREATE FUNCTION udfSequenceTable(
    @MinRange INTEGER = 0,
    @MaxRange INTEGER
)
RETURNS @SequenceTable TABLE(
    ntIndex INTEGER
)
AS
BEGIN

    ;WITH cteSequenceTable
    AS(
         SELECT ntIndex
         FROM(SELECT @MinRange AS ntIndex) ST

         UNION ALL

         SELECT ntIndex + 1
         FROM(SELECT 1 AS ntTemp) ST
         INNER JOIN cteSequenceTable ON  1 =
         WHERE ntIndex  < @MaxRange
    )
    INSERT INTO @SequenceTable
    SELECT ntIndex FROM cteSequenceTable
    OPTION (MAXRECURSION 32767)

    RETURN
END

To execute:

SELECT * FROM udfSequenceTable(1,7)

Output:

ntIndex
1
2
3
4
5
6
7

No comments:

Post a Comment