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?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  	Anthony Main
-- Create date: 18/11/08
-- Description: Produce Table of Dates for Month
-- =============================================
CREATE FUNCTION CalendarTable
(   
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS TABLE 
AS
RETURN 
(
    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
    OPTION  (MAXRECURSION 0)
)
GO

Answers


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 connect.microsoft.com with the issue.


Need Your Help

Python if statement, cannot concatenate 'str' and 'instance' object

python if-statement xml-parsing

My question is I want to check a variable in a xml variable called xmlauthor and I want to check if there is just basically something written on it. How should I do it?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.