How to Pass table name dynamically to SQL Server query or stored procedure?

 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.

 

 

Database

Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.

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.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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'