Common table expression (CTE) in table-valued function

Question

I am used to using common table expressions (CTEs) with MSSQL 2008 R2. I know that the grammar is a bit fiddly about where they can appear, and that in ordinary T-SQL an explicit semicolon (or starting a new batch) is needed before a with expression. I would like to use CTEs in a user-defined function. For simple cases, this works:

create function dbo.udf_test()
returns table
with schemabinding
as
return (
  with foo as (
    select 5 as f
  )
  select f
  from foo
)

But now I would like to make my function udf_test a bit more elaborate. For example to declare a variable inside the body of the function. To start with I need to have explicit begin and end since the function body will no longer be a single statement. So I try to create one thus:

create function dbo.udf_test()
returns table
with schemabinding
as
begin
  return (
    with foo as (
      select 5 as f
    )
    select f
    from foo
  )
end

However this gives the error

Incorrect syntax near the keyword 'with'.

How can I use a CTE in my table-valued function if it is more complex than a single return statement?

Answer 1

CREATE FUNCTION dbo.udf_test()
RETURNS 
@return TABLE 
(
    f int NOT NULL
)
AS
BEGIN

    WITH foo AS (
        SELECT 5 AS f
    )
    INSERT INTO @return
    SELECT f
    FROM foo;

    RETURN;
END