Dynamic access to tables from another database inside an user function
- by Alberto Martinez
I have an user defined table function in SQL Server that aggregate data from several tables including a couple of tables of another database. That is done hardcoding the name of the database in the queries, but we want to make the database name configurable (because our databases usually share the server with the databases of other applications).
I tried to construct a dynamic query string inside the function using the database name that is stored in a configuration table, but:
When I tried exec(@sqlStatement) SQL Server said that execute string is not allowed inside a function.
Then I tried exec sp_executesql @sqlStatement and the function was created, but when you execute it SQL Server says that inside a function you can only run extended functions and procedures.
So the question is: is possible create a function or stored procedure that access a table in another database without having to recreate the function when the database name is different?
TIA.
