In this article I will explain with an example, how to pass Table name as Parameter (Variable) to sp_executesql command 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.
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'
