AS per BOL,
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE
[ WITH [ [,] ...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
In multi-statement table-valued functions, function_body is a series of Transact-SQL statements that populate a table return variable.
The following statements are allowed in the body of a multi-statement function.
May be after RETURN statement that part is not considered as function body.
I think you may have to create a temp table/table variable and fill the data and then return it.
---------------------------------------------------------------------------------