x

T-SQL Does not like Set ???

I am trying to print out a range of Month and Years between two dates. The code fails at the first Set statement.

Create Function RangeOfMonths
(
    @StartDate datetime,
    @EndDate datetime 
)
Returns
@RangeOfMonths Table
(
    MonthName varChar(15),
    YearOfMonth int
)
Set @EndDate = DATEADD(dd,-(DAY(@EndDate)-1),@EndDate)

If @StartDate > @EndDate Exit

While @StartDate < @EndDate
    Begin
       Insert Into @RangeOfMonths
       Select DateName(mm, @StartDate) , DateName(yy,@StartDate)  
       Set @StartDate = DATEADD(MM,1,@StartDate) 
       Return
    End
more ▼

asked Nov 13, 2010 at 02:22 PM in Default

rawilken gravatar image

rawilken
101 8 11 11

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
You need to wrap the function body in a BEGIN ... END block as per http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx.
more ▼

answered Nov 13, 2010 at 02:45 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left

Here's a version of your function that should work. The "exit" keyword does not exist, and your RETURN statement was located too high.

Create Function RangeOfMonths
(
    @StartDate datetime,
    @EndDate datetime 
)
Returns
@RangeOfMonths Table
(
    MonthName varChar(15),
    YearOfMonth int
)
BEGIN
    Set @EndDate = DATEADD(dd,-(DAY(@EndDate)-1),@EndDate);

    --not needed, loop will not execute in this situation
    --If @StartDate > @EndDate RETURN; --not EXIT

    While @StartDate < @EndDate
    Begin
       Insert Into @RangeOfMonths
       Select DateName(mm, @StartDate) , DateName(yy,@StartDate); 
       Set @StartDate = DATEADD(MM,1,@StartDate);
    End
    Return; --moved out of WHILE loop
END
more ▼

answered May 03, 2011 at 05:13 AM

Valentino Vranken gravatar image

Valentino Vranken
1.5k 1 2 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x57
x22

asked: Nov 13, 2010 at 02:22 PM

Seen: 1479 times

Last Updated: Nov 13, 2010 at 02:43 PM