SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using CTE in SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/20/2013 :  06:45:40  Show Profile  Reply with Quote
I have a stored procedure that returns the list of months between given dates. Looks like this:

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


The @querystring opens to something like:
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)


and when ran as is, produces to results I want.

When ran in a stored rocedure, the result is:
Msg 203, Level 16, State 2, Procedure CreateListOfTime, Line 21
The name ... is not a valid identifier.

Could you correct the code for me so that it works?

James K
Flowing Fount of Yak Knowledge

3648 Posts

Posted - 02/20/2013 :  07:44:37  Show Profile  Reply with Quote
You need brackets for exec as in :
exec (@querystring)
But the error message that you would have received is something else if that were the only problem. Nonetheless, you need those brackets. Can you post the error message you get after you add the brackets?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/20/2013 :  07:59:29  Show Profile  Reply with Quote
Hi James,
Good catch.... That is only the reason there
whenever we miss the paranthesis then it will throw following error
Msg 203, Level 16, State 2, Procedure ProcedureName, Line 21
The name ;with cte ......... is not a valid identifier

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  08:31:38  Show Profile  Reply with Quote
few things to note

1. I dont see a termination condition inside CTE MonthlyCalendar so it will recurse forever. I think if you're interested in only 1200 values then make it like

with MonthlyCalendar AS ( SELECT cast('2012-01-01' AS datetime) AS dt
Union ALL
SELECT dateadd(mm, 1, dt)
FROM MonthlyCalendar
WHERE dateadd(mm, 1, dt) <= dt +1200 )

2. TOP without ORDER BY will not guarantee order of resultset so it just means 1200 random records.

3. Always specify a length while casting to varchar

see

http://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/20/2013 :  10:33:57  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  10:40:57  Show Profile  Reply with Quote
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?


Thats the syntax for exec to use string within () unless its called for executing a procedure.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
796 Posts

Posted - 02/20/2013 :  14:12:26  Show Profile  Reply with Quote
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.
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/21/2013 :  05:42:01  Show Profile  Reply with Quote
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.



I would really like to see your solution for this. The task is pretty simple: Receive two dates and return a list of the months in between (inclusive). For example, for inputs Dec 12th 2011 and Dec 14th 2012, return (format doesn't matter)
12/2011
1/2012
2/2012
3/2012
4/2012
5/2012
6/2012
7/2012
8/2012
9/2012
10/2012
11/2012
12/2012
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/21/2013 :  06:41:35  Show Profile  Reply with Quote
Create procedure with two parameters @StartTime and @EndTime

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'


--
Chandu

Edited by - bandi on 02/21/2013 06:50:01
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/21/2013 :  07:13:36  Show Profile  Reply with Quote
Yeah, now it works, thanks!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/21/2013 :  08:40:31  Show Profile  Reply with Quote
quote:
Originally posted by KilpAr

Yeah, now it works, thanks!

Welcome

--
Chandu
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/21/2013 :  09:11:00  Show Profile  Reply with Quote
Going forward from here, I also need one that returns the list of weeks.

Here's the current situation:

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'


Now while this otherwise works, I have problems with the week 53. When I run the code like:
DECLARE	@return_value int

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

GO


the answer is:
50 2009
51 2009
52 2009
53 2010
1 2010

i.e. week 53 has wrong year.

edit. Seems like changing the start date to
SET @StartTime = (select DATEADD(DAY,-(DATEPART(dw, @StartTime)-2),@StartTime))
helped

Edited by - KilpAr on 02/21/2013 09:20:05
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/21/2013 :  15:27:52  Show Profile  Reply with Quote
there is a differencet bwtween ISO_WEEK and WEEK. But, maybe you want to start at eh begining of teh week for the start date?
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)
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/21/2013 :  09:40:24  Show Profile  Reply with Quote
Just to show where this continued to (and a working example of that week lister), check here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183893
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000