Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-02-20 : 06:45:40
|
I have a stored procedure that returns the list of months between given dates. Looks like this:CREATE PROC [dbo].[CreateListOfTime]@StartTime date,@EndTime dateASDECLARE @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 21The name ... is not a valid identifier.Could you correct the code for me so that it works? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 07:44:37
|
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? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-20 : 07:59:29
|
Hi James,Good catch.... That is only the reason therewhenever we miss the paranthesis then it will throw following errorMsg 203, Level 16, State 2, Procedure ProcedureName, Line 21The name ;with cte ......... is not a valid identifier--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 08:31:38
|
few things to note1. 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 likewith 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 varcharseehttp://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-02-20 : 10:33:57
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 10:40:57
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-02-20 : 14:12:26
|
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. |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-02-21 : 05:42:01
|
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/20111/20122/20123/20124/20125/20126/20127/20128/20129/201210/201211/201212/2012 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 06:41:35
|
Create procedure with two parameters @StartTime and @EndTimeDECLARE @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 MonthlyCalendarOPTION (maxrecursion 0)Procedure looks like thisGOCREATE PROCEDURE [dbo].[CreateListOfTime]( @StartTime date, @EndTime date)ASBEGIN ;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)ENDGOEXEC [dbo].[CreateListOfTime] @StartTime ='12/12/2011', @EndTime = '14/12/2012' --Chandu |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-02-21 : 07:13:36
|
Yeah, now it works, thanks! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 08:40:31
|
quote: Originally posted by KilpAr Yeah, now it works, thanks!
Welcome--Chandu |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-02-21 : 09:11:00
|
Going forward from here, I also need one that returns the list of weeks.Here's the current situation:USE [CORPDATA]GOALTER PROCEDURE [dbo].[CreateListOfWeeks]( @StartTime date, @EndTime date)ASBEGIN ;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)ENDGOEXEC [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 intEXEC @return_value = [dbo].[CreateListOfWeeks] @StartTime = '2009-12-12', @EndTime = '2010-01-14'GO the answer is:50 200951 200952 200953 20101 2010i.e. week 53 has wrong year.edit. Seems like changing the start date toSET @StartTime = (select DATEADD(DAY,-(DATEPART(dw, @StartTime)-2),@StartTime))helped |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-21 : 15:27:52
|
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) |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-21 : 09:40:24
|
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 |
|
|
|
|
|
|
|