Execute WITH statement inside a function

Question

I have the following code:

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

I need to run this code inside a function, however I just can't make the syntax right. Here's how it is right now:

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

If I try to execute this, it gives me this error:

Msg 156, Level 15, State 1, Procedure FN_INDICE_SPLIT, Line 4
Incorrect syntax near the keyword 'RETURN'.

I've tried to do this in many ways, but I keep getting syntax errors and I don't know what's wrong.

Answer 1

You don't need the semicolon before the WITH in a TABLE-VALUED FUNCTION. Especially considering that you cannot even have multi-statements in a TVF, there's no reason for a statement delimiter to be present.

The correct form is CREATE FUNCTION (...) RETURNS TABLE AS RETURN <statement>

CREATE FUNCTION [dbo].[FN_INDICE_SPLIT]
    (@sInputList VARCHAR(8000),@sDelimiter VARCHAR(8000),@INDICE INT)
RETURNS TABLE 
AS RETURN
WITH OrderedOrders AS
( 
SELECT *,  ROW_NUMBER() OVER (ORDER BY item) AS RowNumber
 from  dbo.fnSplit(@sDelimiter, @INDICE)  
)
select ITEM from OrderedOrders where RowNumber=@INDICE
GO

Answer 2

You should use RETURN*S*

RETURNS TABLE