Execute WITH statement inside a function

function, sql-server, t-sql

Solution

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

Problem

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.

Original source