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