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

avatar image

rawilken
101 10 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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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

avatar image

Valentino Vranken
1.5k 2 4 12

(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.

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:

x1069
x68
x27

asked: Nov 13, 2010 at 02:22 PM

Seen: 1703 times

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

Copyright 2016 Redgate Software. Privacy Policy