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 next 15 dates from today. And he wanted to do this by using cursors. While you can write a cursor to get the results in this case, but cursor 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 number 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