我有以下代码：
WITH OrderedOrders AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY item) AS RowNumber
from dbo.fnSplit('1:2:3:5',':')
)
select * from OrderedOrders where rownumber =2
我需要在函数内运行此代码,但是我无法使语法正确.现在的情况如下：
CREATE FUNCTION [dbo].[FN_INDICE_SPLIT]
(@sInputList VARCHAR(8000),@sDelimiter VARCHAR(8000),@INDICE INT)
RETURN TABLE
;WITH OrderedOrders AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY item) AS RowNumber
from dbo.fnSplit(@sDelimiter,@INDICE)
)
select ITEM from OrderedOrders where RowNumber=@INDICE
如果我尝试执行此操作,它会给我这个错误：
Msg 156,Level 15,State 1,Procedure FN_INDICE_SPLIT,Line 4 Incorrect Syntax near the keyword 'RETURN'.
我试图在很多方面做到这一点,但我不断得到语法错误,我不知道出了什么问题.
解决方法
在TABLE-VALUED FUNCTION中,您不需要在WITH之前使用分号.特别是考虑到你甚至无法在TVF中使用多语句,因此没有理由存在语句分隔符.
正确的形式是CREATE FUNCTION(…)RETURNS TABLE AS RETURN< statement>
CREATE FUNCTION [dbo].[FN_INDICE_SPLIT]
(@sInputList VARCHAR(8000),@INDICE INT)
RETURNS TABLE
AS RETURN
WITH OrderedOrders AS
(
SELECT *,@INDICE)
)
select ITEM from OrderedOrders where RowNumber=@INDICE
GO
