Dynamic Server Name in SQL Statements (from clause)
Dynamic SQL will work. I have written similar things in SQL Server 2000 this way.

EXEC() can take a string variable as input. Construct this string as you show in the question, using the current DB context.

Instead of table variables, however, use temporary tables (#SomeName). These are visible in nested scopes, once defined. So create the #Table, then construct the SQL string referencing the temp table, then EXEC that string.