In this article I will explain with an example, how to pass Table name dynamically to a query or stored procedure in SQL Server.
The sp_executesql command supports accepting Table name as Parameter (Variable) in the following SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012, 2014, 2017, 2019 and higher.
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Passing Table name as Parameter to sp_executesql command in SQL Server
In the following SQL Query, the name of the Table is appended to the dynamic SQL string.
Note: The sp_executesql command will not accept Table Name as parameter and hence we need to append the Table Name directly to the dynamic SQL string.
Finally, the SQL Query is executed using sp_executesql command in SQL Server.
SET @Table_Name = 'Employees'
SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
EXECUTE sp_executesql @DynamicSQL
Passing Table name as Parameter to a Stored Procedure
The following Stored Procedure accepts the Table Name as parameter and then a dynamic SQL string is built to which the name of the Table is appended.
Finally, using the sp_executesql command the dynamic SQL string is executed.
    @Table_Name SYSNAME
    DECLARE @DynamicSQL NVARCHAR(4000)
    SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
    EXECUTE sp_executesql @DynamicSQL
Executing the Stored Procedure with dynamic Table name
The above Stored Procedure can be executed as show below. The name of the Employees table is passed as parameter to the Stored Procedure and it will SELECT all records from the Employees table.
EXEC Dynamic_SP 'Employees'