我想要写一个这样的函数:
CREATE OR ALTER FUNCTION TestFunction()
RETURNS TABLE
AS RETURN
WITH NumberList AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM NumberList
WHERE Number < 1000
)
SELECT Number
FROM NumberList
OPTION (MAXRECURSION 0)
但是,在运行此查询时,SQL Server返回以下错误:
Msg 156, Level 15, State 1, Procedure TestFunction, Line 13 [Batch Start Line 0]
Incorrect syntax near the keyword 'OPTION'.
如果我从函数中删除第OPTION (MAXRECURSION 0)
行,它会起作用:
CREATE OR ALTER FUNCTION TestFunction()
RETURNS TABLE
AS RETURN
WITH NumberList AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM NumberList
WHERE Number < 1000
)
SELECT Number
FROM NumberList
但在执行此函数时:
SELECT *
FROM TestFunction()
然后,它返回以下错误:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
是否可以在函数内添加第OPTION (MAXRECURSION 0)
条?我认为对于函数的用户来说,在执行函数时不必记住添加OPTION子句会很方便.