I can't create such a function, why? It gives em the following error:
Msg 197, Level 15, State 1, Procedure TEST_TABLE, Line 20
EXECUTE cannot be used as a source when inserting into a table variable.
The thing is, the table name I'm trying to select from is defined dynamically from this statement:
(select Request_Table_Name dbo.Request_Type where Request_Type_ID=[SOME_ID])
Also, the number those tables that I'm trying to select from are a lot, and don't have a standarized naming convension for them, therefore I need to get their names dynamically like the above attempt.
CREATE FUNCTION TEST_TABLE
declare @MySQL varchar(200)
set @MySQL = 'select Application_ID, Date_of_Submission from (select Request_Table_Name dbo.Request_Type where Request_Type_ID='+@RequestTypeID+')'
EXEC sp_executesql @MySQL