Generate Range of Values (Dates or Numbers) for SQL queries

Many times when querying SQL you'll need to join against a date range, or a number range. This can be done less-elegantly with a while loop, or using CTEs.
(All of the examples assume SQL 2008 or later, due to "date" data type.)

Using a while-loop, into a temp-table:

DECLARE @date DATE, @EndDate DATE
SELECT @date='2012-01-01', @EndDate='2013-01-01'
DECLARE @dateRange TABLE (dateValue DATE)
WHILE @date <= @EndDate
BEGIN
  INSERT INTO @dateRange VALUES (@date)
  SET @date = DATEADD(d,1,@date)
END

A date range using CTE:

DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate='2012-01-01', @EndDate='2013-01-01'
;WITH cteRange AS
(
  SELECT @StartDate as [DateValue]
  UNION ALL
  SELECT DATEADD(d,1,DateValue)
  FROM cteRange
  WHERE DATEADD(d,1,DateValue) <= @EndDate
)
SELECT DateValue
FROM cteRange
OPTION (MAXRECURSION 0)

Or for an integer range:

DECLARE @Start int, @End int
SELECT @Start=1, @End=100
;WITH cteRange AS
(
  SELECT @Start as theNumber
  UNION ALL
  SELECT theNumber + 1
  FROM cteRange
  WHERE theNumber + 1 <= @End
)
SELECT theNumber
FROM cteRange
OPTION (MAXRECURSION 0)