Can you have a WITH statement in a tabular user defined function

I have the following code for a UDF but it errors with the message:

Msg 156, Level 15, State 1, Procedure CalendarTable, Line 39 Incorrect syntax near the keyword 'OPTION'.

is it because of my WITH statement as I can run the same code fine in a stored procedure?

-- =============================================
-- Author:      Anthony Main
-- Create date: 18/11/08
-- Description: Produce Table of Dates for Month
-- =============================================
    @StartDate DATETIME,
    @EndDate DATETIME
    with    MyCalendar as
            select  cast(@StartDate as datetime) DateValue
            union   all
            select  DateValue + 1
            from    MyCalendar   
            where   DateValue + 1 <= @EndDate

    select  DateValue
    ,       datepart(dy, DateValue) [day of year]
    ,       datename(dw, DateValue) [day]
    ,       datepart(dw, DateValue-1) [day of week]
    ,       datepart(dd, DateValue) [day of month]
    ,       datepart(ww, DateValue) [week]
    ,       datepart(mm, DateValue) [month]
    ,       datename(mm, DateValue) [month]
    ,       datepart(qq, DateValue) [quarter]
    ,       datepart(yy, DateValue) [year]
    ,       datepart(HH, DateValue) [HOUR]
    ,       datepart(MI, DateValue) [MIN]
    ,       datepart(SS, DateValue) [SEC]
    ,       datepart(MS, DateValue) [MILLISECOND]
    from    MyCalendar


No, you can't use the OPTION keyword.

From the documentation: "MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two."

If you explain what you want to query (what do you want the end results to look like), we could write a better SQL statement that doesn't need recursion.

From what I can tell, OPTION MAXRECURSION is not allowed in a UDF. There is an item at with the issue.

Are you missing a closing bracket here? (the closing bracket for "AS RETURN ("

You can't use the OPTION in the UDF. Also, since UDFs are usually called in high volume, it probably makes more sense to generate your date range by using a Numbers table (you throw a few thousand numbers in a table - from 1 to 1,000,000, say) and generate your date range table in the UDF from that.

Here's a link to a good set of techniques which use such a utility table - often called a Numbers table or a Pivot table.

Need Your Help

Can ArangoDB scale like MongoDB or CouchDB

nosql arangodb

I'm reading about ArangoDB and it is more interesting but I can't find where in the documentation how ArangoDB scales. Does ArangoDB scale and can it use sharding like MongoDB or CouchDB?