CREATE PROC [dbo].[CreateListOfTime]
@StartTime date,
@EndTime date
AS
DECLARE @querystring varchar(max);

SET @querystring = ';with MonthlyCalendar AS ( SELECT cast(''' + convert(varchar, @starttime) + ''' AS datetime) AS dt 
Union ALL 
SELECT dateadd(mm, 1, dt) 
FROM MonthlyCalendar ) 
SELECT dateadd(month,datediff(m,0,paivays),0) AS Monthlist 
FROM ( 
SELECT TOP 1200 dt AS paivays 
FROM MonthlyCalendar ) AS list 
WHERE list.Paivays  >= ''' + convert(varchar, @starttime) + '''
AND 
list.Paivays < ''' + convert(varchar, @EndTime) + ''' 
OPTION (maxrecursion 0)'
exec @querystring

with MonthlyCalendar AS ( SELECT cast('2012-01-01' AS datetime) AS dt 
Union ALL 
SELECT dateadd(mm, 1, dt) 
FROM MonthlyCalendar ) 
SELECT dateadd(month,datediff(m,0,paivays),0) AS Monthlist 
FROM ( 
SELECT TOP 1200 dt AS paivays 
FROM MonthlyCalendar ) AS list 
WHERE list.Paivays  >= '2012-01-01'
AND 
list.Paivays < '2013-12-31' 
OPTION (maxrecursion 0)

exec (@querystring)

quote:

---

Originally posted by KilpAr

I'm not sure if it recurses forever, but at least it is very fast (I mean, less than a second). The TOP is basically never used there, when you select from a span of, say, two years 120 or less distinct months, there's not too many options how it can come out. But I'll add the ORDER BY there. Also I'll add that recursing limit. And that varchar length. Thanks for these, I always appreciate to get little hints like these!

Also, now the code otherwise more or less works, have to continue with it tomorrow at work.

Is there a reason to leave the brackets out after exec? I mean, do I need to learn when to use them and when not, or can I just stick them there always? If not, how do I know when to use them and when not?

---

quote:

---

Originally posted by jeffw8713

I don't see any reason here to use dynamic SQL. I would recommend removing that altogether - and that way you don't have to worry about how to call the string.

---

DECLARE @StartTime date = '12/12/2011' , @EndTime date = '14/12/2012'

;with MonthlyCalendar 
AS 
(	SELECT cast(@StartTime AS datetime) AS dt 
	Union ALL 
	SELECT dateadd(mm, 1, dt) 
		FROM MonthlyCalendar 
		WHERE dateadd(mm, 1, dt) <= @EndTime
)
SELECT * FROM MonthlyCalendar
OPTION (maxrecursion 0)


Procedure looks like this

GO
CREATE PROCEDURE [dbo].[CreateListOfTime](
	@StartTime		date,
	@EndTime		date
)
AS
BEGIN
	;with MonthlyCalendar 
	AS 
	(	SELECT cast(@StartTime AS datetime) AS dt 
		Union ALL 
		SELECT dateadd(mm, 1, dt) 
			FROM MonthlyCalendar 
			WHERE dateadd(mm, 1, dt) <= @EndTime
	)
	SELECT RIGHT(CONVERT( VARCHAR(10), dt, 105),7) [MM-YYYY] FROM MonthlyCalendar
	OPTION (maxrecursion 0)
END
GO
EXEC [dbo].[CreateListOfTime] @StartTime ='12/12/2011', @EndTime = '14/12/2012'

quote:

---

Originally posted by KilpAr

Yeah, now it works, thanks!

---

USE [CORPDATA]
GO
ALTER PROCEDURE [dbo].[CreateListOfWeeks](
	@StartTime		date,
	@EndTime		date
)
AS
BEGIN
	;with WeeklyCalendar 
	AS 
	(	
		SELECT cast(@StartTime AS datetime) AS dt 
		Union ALL 
		SELECT dateadd(DAY, 7, dt) 
		FROM WeeklyCalendar 
		WHERE dateadd(WEEK, 1, dt) <= @EndTime
	)
	SELECT DATEPART(ISO_WEEK, dt) as w, DATEPART(YEAR, dt) as y FROM WeeklyCalendar WHERE dt BETWEEN @StartTime AND @EndTime
	ORDER BY dt
	OPTION (maxrecursion 0)
END
GO
EXEC [dbo].[CreateListOfWeeks] @StartTime = '2011-12-12', @EndTime = '2012-12-14'

DECLARE	@return_value int

EXEC	@return_value = [dbo].[CreateListOfWeeks]
		@StartTime = '2009-12-12',
		@EndTime = '2010-01-14'

GO

DECLARE	@StartTime date = '2009-12-12';
DECLARE @EndTime date = '2010-01-14'




	;with WeeklyCalendar 
	AS 
	(	
		SELECT cast(DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartTime), 0) AS datetime) AS dt 
		Union ALL 
		SELECT dateadd(DAY, 7, dt) 
		FROM WeeklyCalendar 
		WHERE dateadd(WEEK, 1, dt) <= @EndTime
	)
	SELECT *, DATEPART(ISO_WEEK, dt) as w, DATEPART(YEAR, dt) as y FROM WeeklyCalendar WHERE dt BETWEEN @StartTime AND @EndTime
	ORDER BY dt
	OPTION (maxrecursion 0)