Pass table name dynamically to SQL Server query or Store Procedure

Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained the implementation about how to pass Table name dynamically to SQL Server query or Store Procedure. 

Join our Channel  on Whtsapp and Telagram for All Updates                                          

 

The sp_executesql command can be accepting name of the table as their Parameter (Variable). This article is applicable for all versions of SQL Server i.e. 2000, 2005, 2008, 2012, 2014, 2019, 2022 and higher.

Database

In this article we used of Microsoft’s Northwind Database. You can download Microsoft’s Northwind Database from Microsoft’s Website.

Pass Table name as Parameter to sp_executesql command in SQL Server

In this SQL Query, the name of the Table will be attach or add to the dynamic SQL string. The sp_executesql command will not accept Table Name as parameter so here we need to attach or add the Table Name directly to the dynamic SQL string.

The SQL Query will be executed by 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 Store 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_StoreProcedure]

    @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

Execute Stored Procedurewith the dynamic Table name

We can execute he above created Store Procedure as below.

The name of the Employees table will be passed as parameter to the Store Procedure and it will SELECT all records from the Employees table.

EXEC Dynamic_StoreProcedure 'Employees'

Conclusion: In above code, I have been explained implementation about how to pass Table name dynamically to SQL Server query or Store Procedure. This code is very helpful for every developer. Bye bye and take care of yourself Developers. We will come back shortly with the new article.

Regards

Programming Hub