I need to have a function to get all the week numbers in a given date range. I have to seperate this from my stored procedure since it has a lengthy process.
But when I use #tables in side the function it gives following error
Msg 2772, Level 16, State 1, Procedure WeekNumbersWithinRange, Line 19
Cannot access temporary tables from within a function.
And here is my function.
CREATE FUNCTION WeekNumbersWithinRange
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS TABLE
AS
BEGIN
IF OBJECT_ID (N'tempdb..#WeeksofRange', N'U') IS NOT NULL
BEGIN
DROP TABLE #WeeksofRange
END
CREATE TABLE #WeeksofRange (WeekNo INT);
-- Get all week numbers for the given delivery date range
IF OBJECT_ID (N'tempdb..#Calendar', N'U') IS NOT NULL
BEGIN
DROP TABLE #Calendar
END
CREATE TABLE #Calendar
(
CalendarDate DATE PRIMARY KEY,
IsWeekend BIT,
YearNo SMALLINT,
QuarterNo TINYINT,
MonthNo TINYINT,
DayOfYearNo SMALLINT,
DayNo TINYINT,
WeekNo TINYINT,
WeekDayNo TINYINT
)
DECLARE @beginDate DATE, @endDate DATE
SELECT
@beginDate = @FromDate, @endDate = @ToDate
WHILE @beginDate <= @endDate
BEGIN
INSERT INTO #Calendar (CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo)
SELECT
@beginDate As CalendarDate,
(CASE WHEN DATEPART(Weekday, @beginDate) IN (7, 1)
THEN 1 ELSE 0
END) AS IsWeekend,
DATEPART(Year, @beginDate) AS YearNo,
DATEPART(QUARTER, @beginDate) AS QuarterNo,
DATEPART(MONTH, @beginDate) AS MonthNo,
DATEPART(DayOfYear, @beginDate) AS DayOfYearNo,
DATEPART(Day, @beginDate) AS DayNo,
DATEPART(Week, @beginDate) AS WeekNo,
DATEPART(WEEKDAY, @beginDate) AS WeekDayNo
--,(Case When @beginDate < '02/03/2011' Then 0 Else DATEPART(Week, @beginDate) - 5 End) As mySpecificWeekNo
SET @beginDate = DATEADD(Day, 1, @beginDate)
END
INSERT INTO #WeeksofRange
SELECT DISTINCT WeekNo
FROM #Calendar;
-- End of Select all week numbers in the range
SELECT * FROM #WeeksofRange
GO
Is there any other way to do this? Or do I need to write this logic inside my stored procedure?