You can include table names or derived tables in your from clause but not a non-static value. Dynamic SQL (using sp_executesql not EXEC) is the way to go for this.There is no problem with Dynamic SQL when it done correctly (e.g. in a way where SQL Injection can't happen).
That said, here is an alternative solution if you MUST avoid Dynamic SQL.
IF OBJECT_ID('tempdb..#tbl1') IS NOT NULL DROP TABLE #tbl1;
IF OBJECT_ID('tempdb..#tbl2') IS NOT NULL DROP TABLE #tbl2;
IF OBJECT_ID('tempdb..#tbl3') IS NOT NULL DROP TABLE #tbl3;
IF OBJECT_ID('tempdb..#tbls') IS NOT NULL DROP TABLE #tbls;
SELECT 't1' AS tbl INTO #tbl1;SELECT 't2' AS tbl INTO #tbl2;SELECT 't3' AS tbl INTO #tbl3;
-- catch-all query
WITH tbls(tbl) AS (SELECT '#tbl1' UNION SELECT '#tbl2' UNION SELECT '#tbl3')
SELECT * INTO #tbls FROM tbls
DECLARE @CN VARCHAR (100)='#tbl1';
IF EXISTS (SELECT tbl FROM #tbls WHERE tbl=@CN)
SELECT * FROM #tbl1
--SELECT TOP 10 * FROM #tbl1
SELECT * FROM #tbl2
--SELECT TOP 10 * FROM #tbl2
--SELECT TOP 10 * FROM #tbl3
SELECT * FROM #tbl3
SELECT 'table does not exist or has not been entered into #tbls. Fire someone.' AS Msg
Here I am using a catch-all query; see this article
by Gail Shaw for more information about catch-all queries... One final important note
Do you really need all the rows from any table defined by @CN? Even if it has a couple billion rows?
I would suggest that you have the query return a limited number of values (note my commented out code above)...
-- Alan Burstein
Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K
. "I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001