How to set the maxrecursion option for a CTE inside a Table-Valued-Function

by

From this MSDN forums thread I learn that

[the] OPTION clause can be used only at the statement level

So you cannot use it within a query expression inside view definitions or inline TVFs etc. The only way to use it in your case is to create the TVF without the OPTION clause and specify it in the query that uses the TVF. We have a bug that tracks request for allowing use of OPTION clause inside any query expression (for example, if exists() or CTE or view).

and further

You can not change the default value of that option inside a udf. You
will have to do it in the statement referencing the udf.

So in your example, you must specify the OPTION when you call your function:

 CREATE FUNCTION [liste_jour]  
 (@debut date,@fin date)
 RETURNS TABLE
 AS     
 RETURN      
 (  
  WITH CTE as(       
  SELECT @debut as jour       
  UNION  ALL       
  SELECT DATEADD(day, 1, jour)       
  FROM   CTE      
  WHERE  DATEADD(day, 1, jour) <= @fin)
  SELECT jour FROM CTE -- no OPTION here
 )

(later)

SELECT * FROM [liste_jour] ( @from , @to ) OPTION ( MAXRECURSION 365 )

Note that you can’t work round this by having a second TVF that just does the above line – you get the same error, if you try. “[the] OPTION clause can be used only at the statement level”, and that’s final (for now).

More Related Contents:

  1. SQL Server CTE and recursion example
  2. Which are more performant, CTE or temporary tables?
  3. CTE Recursion to get tree hierarchy
  4. Infinite loop CTE with OPTION (maxrecursion 0)
  5. CTE to get all children (descendants) of a parent
  6. Recursive sum in tree structure
  7. SQL Recursive CTE: Finding objects linked by property
  8. how to select next n letters after one specific letter using function in sql server by giving function name,specific letter, no of letters next
  9. SQL server query to output the data shown below
  10. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  11. What is the difference between Integrated Security = True and Integrated Security = SSPI?
  12. How do you clear the SQL Server transaction log?
  13. How to use a variable for the database name in T-SQL?
  14. Order Of Execution of the SQL query
  15. What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
  16. Missing artifact com.microsoft.sqlserver:sqljdbc4:jar:4.0
  17. Create a view with ORDER BY clause
  18. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  19. INSERT INTO vs SELECT INTO
  20. Possible to store value of one select column and use it for the next one?
  21. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  22. How to write UTF-8 characters using bulk insert in SQL Server?
  23. How to: Create trigger for auto update modified date with SQL Server 2008
  24. How can I Insert data into SQL Server using VBNet
  25. Using NOLOCK Hint in EF4?
  26. Remove all spaces from a string in SQL Server
  27. Retrieving the date in SQL Server; CURRENT_TIMESTAMP vs GetDate()
  28. Milliseconds wrong when converting from XML to SQL Server datetime
  29. Using GROUP BY with FIRST_VALUE and LAST_VALUE
  30. How can I get the list of tables in the stored procedure?

Leave a Comment