In this article I will explain with an example, how to pass Table name dynamically to SQL Server query or Stored Procedure.
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.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 

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.
DECLARE @Table_Name SYSNAME, @DynamicSQL NVARCHAR(4000)
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.
CREATE PROCEDURE [Dynamic_SP]
    @Table_Name SYSNAME
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @DynamicSQL NVARCHAR(4000)
    SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
    EXECUTE sp_executesql @DynamicSQL
END
GO
 
 

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'