This is another tip in SQL Server that I decided to share is how to pass table name dynamically to a query or stored procedure.
Means the same query can be used on multiple tables if they have same structure.
DECLARE @Table_Name sysname, @DynamicSQL nvarchar(4000)
SET @Table_Name = 'Employees'
SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
EXECUTE sp_executesql @DynamicSQL
The above query is dynamically build and executed on the table based on the table name that is passed.
If you need to do the above with a stored procedure you can do in the following way
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
And to execute the stored procedure
EXEC Dynamic_SP 'Employees'