Run WITH statement inside 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, but I just can't make the syntax correctly. Here's how it is 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 accomplish this, it will give me this error:

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

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

Answer 1

You do not need a semicolon before WITH in the TABLE-VALUED FUNCTION function. Especially considering that you cannot even have multitasking in TVF, there is no reason for the presence of an instruction separator.

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 must use RETURN * S *

 RETURNS TABLE