Generating a Sequence in SQL

Introduction

I came through one of the many interesting SQL questions on one of the forums yesterday. The original poster of the question wanted to get a list of the next 15 dates starting today. And he wanted to do this by using cursors. While you can write a cursor to get the results in this case, cursors are not meant to be used like this. A cursor is used to step through the results of a query that returns multiple results, e.g., rows from a table. One good old way of generating a sequence of next n dates would be to use the WHILE loop in SQL.

    DECLARE @LoopCount INT
    SET @LoopCount = 0
     
    DECLARE @calcDate DateTime
    SET @calcDate = GETDATE() 

    DECLARE @outDate varchar(30) 

    WHILE @LoopCount < 15
    BEGIN
     
         SET @outDate = CONVERT(NVARCHAR, @calcDate, 103) 
         PRINT @outDate
         SET @calcDate = DATEADD(dd, 1, @calcDate) 

         SET @LoopCount = @LoopCount + 1
    END

Generating a Sequence of Dates in SQL

Another better alternative would be to use the Common Table Expressions in SQL Server.

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

    WITH q AS
        (
        SELECT  GETDATE() AS datum
        UNION ALL
        SELECT  datum + 1
        FROM    q
        WHERE datum + 1 < GETDATE() + 15
        )
    SELECT  CONVERT(NVARCHAR, DATEADD(dd, DATEDIFF(dd, 0, datum), 0), 103) AS MyDate
    FROM    q

Generating a Sequence of Numbers in SQL

Similarly, you can generate the sequence of numbers in SQL using the Common Table Expressions.

    WITH q AS
        (
        SELECT  1 AS num
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE num < 15
        )
    SELECT  * FROM    q

Generating a Sequence of Alphabets in SQL

Or, you can even generate the sequence of alphabets in SQL using the Common Table Expressions.

    WITH q AS
        (
        SELECT  0 AS num
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE num <= 26
        )
        SELECT CHAR(num + 65) FROM    q