I have figured out a solution to this problem. We can build a Function or View with "rendered" sql in a stored procedure that can then be executed as normal.
1.Create another sproc
CREATE PROCEDURE [dbo].[usp_FunctionBuilder]
DECLARE @outerSql VARCHAR(MAX)
DECLARE @innerSql VARCHAR(MAX)
2.Build the dynamic sql that you want to execute in your function (Example: you could use a loop and union, you could read in another sproc, use if statements and parameters for conditional sql, etc.)
SET @innerSql = 'your sql'
3.Wrap the @innerSql in a create function statement and define any external parameters that you have used in the @innerSql so they can be passed into the generated function.
SET @outerSql = 'CREATE FUNCTION [dbo].[fn_GeneratedFunction] ( @Param varchar(10))
RETURNS TABLE
AS
RETURN
' + @innerSql;
EXEC(@outerSql)
This is just pseudocode but the solution solves many problems such as linked server limitations, parameters, dynamic sql in function, dynamic server/database/table name, loops, etc.
You will need to tweak it to your needs, (Example: changing the return in the function)