Execute Dynamic SQL in table-valued UDF: How to (or equivalent)

If I try to create this function, I get an error:

CREATE FUNCTION udf_test()
RETURNS @DATER TABLE
(
DATE_THEN datetime --collate SQL_Latin1_General_CP850_BIN2 NOT NULL
)
AS
BEGIN
DECLARE @SQL_STATMENT_TX nvarchar(4000)
SET @SQL_STATMENT_TX = 'SELECT getdate() where ''x'' = ''x'''
EXEC (@SQL_STATMENT_TX)
RETURN
END

Msg 443, Level 16, State 14, Procedure udf_test, Line 10
Invalid use of a side-effecting operator 'EXECUTE STRING' within a
function.

These three lines execute fine:
DECLARE @SQL_STATMENT_TX nvarchar(4000)
SET @SQL_STATMENT_TX = 'SELECT getdate() where ''x'' = ''x'''
EXEC (@SQL_STATMENT_TX)

Why would I want to make such a stupid function? This is just an
example, not the actual function.

I am working with vendor product (re-design is out of the question).
I need to create a set of values from a table(s) that is (are) not
known until run time. I then need to return that list as a table that
can be joined to other tables in regular queries.

I don't think a stored proc that returns a table will work, cause I
think the table that is returned goes out of scope once the proc stops
running, and I need the returned values to be available for joining
into the user's query.

If it sounds convoluted, that's because it is. Here's another shot at
explaining:

1. User is going to build and run a query, but I don't know what it
is. Query is likely complex, and may or may not be stored as a view.
2. User needs to join his query to a set of values based on some
arguments that user provides at runtime.
3. Based on user arguments, I have to join some tables and generate
the set of values for #3. The involved tables and columns depend on
the user arguments.
4. I want to return my results to user as a table that s/he can join
into his query.

Any ides how to do this? Remember, I can't redesign, I am stuck with
the vendor's table designs. I want to avoid writing CLR code if
possible (I really like doing stuff in the database).

Thanks,

Bill
.

Relevant Pages

  • Re: Can I optimize this query?
    ... One of which is using dynamic sql or building the query in the ... > I have written a stored proc that supports a MS access front-end app> which allows to pass 4 or a combo of any four parameters. ... > declare @contracttemp varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: Compare Substrings
    ... > query a lot of records are sent as result but i expect no ... declare @P1 bigint set @P1=NULL exec Content_Save ...
    (microsoft.public.sqlserver.server)
  • Re: Help creating a stored procedure to call from .Net
    ... > DECLARE @BackupFile varchar ... > DECLARE @query varchar ... > EXEC ... > INSERT #restoretemp EXEC ...
    (microsoft.public.dotnet.languages.vb)
  • File does not exist error
    ... use Northwind ... DECLARE @SQL_STRING AS ... The query will execute successfully. ... EXEC sp_ExecuteSql @SQL_STRING ...
    (microsoft.public.sqlserver.programming)
  • Re: Table as a variable
    ... depositing the table name from my table-list data. ... I think there is problrm with the query it should be ... exec ... DECLARE @query varchar ...
    (microsoft.public.sqlserver.clients)