Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / SQL
Tip/Trick

Generating a Sequence in SQL

,
Rate me:
Please Sign up or sign in to vote.
4.92/5 (7 votes)
8 May 2014CPOL1 min read 17.5K   8   4
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.

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.
SQL
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.

SQL
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.

SQL
WITH q AS
    (
    SELECT  0 AS num
    UNION ALL
    SELECT  num + 1
    FROM    q
    WHERE num <= 26
    )
    SELECT CHAR(num + 65) FROM    q
This article was originally posted at http://manasbhardwaj.net/generating-sequence-sql

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United Kingdom United Kingdom
Slowing down. Now working as a "Business Automation Consultant".

"Abandon all hope of ever being understood, all ye who have a good idea but don't say it perfectly." Doug Bernard

Written By
Architect
Netherlands Netherlands

Read my personal blog at www.manasbhardwaj.net.


Comments and Discussions

 
Suggestionan better alternate Pin
Thava Rajan14-May-14 4:15
professionalThava Rajan14-May-14 4:15 
GeneralRe: an better alternate Pin
CHill6015-May-14 6:53
mveCHill6015-May-14 6:53 
GeneralRe: an better alternate Pin
Thava Rajan16-May-14 3:17
professionalThava Rajan16-May-14 3:17 
well, i accept your argument, but i am just suggest an alternate way, well what about
Tally table concept[^], it is better than the cursor and cte
QuestionThank you! Pin
CHill609-May-14 1:24
mveCHill609-May-14 1:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.