UDF - Return a table from a custom sql string execution with "Exec 'select * from ....' "

How can I return a table from an UDF that executes a custom query string? Eg:

CREATE FUNCTION fx_AdvancedSearch
(

    @keywords varchar(255),
        (..... other params ....)
)
RETURNS TABLE 
AS
BEGIN
    DECLARE @SqlQuery varchar(开发者_如何学JAVA1000)

.....

    SET @SqlQuery = 'my custom select  string previously generated'

EXEC(@SqlQuery)

END

I was thinking maybe i could create a temp in memory table and then return it, but i don't want to allocate extra memory in the server for this purpose.

And...should i use a SP or an UDF to accomplished this goal? I have this running in a SP but the Linq2SQL cannot determine the return type and therefore i cannot use this function properly in my .NET code.

Thanks! TT

Erland has documented a lot of different ways to accomplish this (but not with functions, as someone else already pointed out), see: http://sommarskog.se/share_data.html ... may also require reading of his two dynamic SQL articles.

I'm fairly sure you cannot use dynamic sql in a function.