title: Execute WITH statement inside a function
body: <p>I have the following code:</p>
  
  <pre><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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>If I try to execute this, it gives me this error:</p>
  
  <pre><code>Msg 156, Level 15, State 1, Procedure FN_INDICE_SPLIT, Line 4
  Incorrect syntax near the keyword 'RETURN'.
  </code></pre>
  
  <p>I've tried to do this in many ways, but I keep getting syntax errors and I don't know what's wrong.</p>