SQL using #table/ Temp table inside a table valued function

Viewed 9525

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?

2 Answers
Related