Dynamic Table Name as a Variable in SQL Server

In SQL Server, you can use the table name as a variable and use Dynamic SQL(sp_executesql) to execute a query dynamically. In this blog, let us explore the different methods to use dynamic table names as a variable in SQL Server.

Table of Contents:

• Why do we need to use the Dynamic Table Name as a Variable in SQL Server?
• Advantages of using Dynamic Table Name as a variable in SQL Server
• Methods to use Dynamic Table Name as a Variable in SQL Server
  • Method 1: Using sp_executesql in SQL Server
  • Method 2: Using EXEC in SQL Server
  • Method 3: Using Parameters with sp_executesql in SQL Server
  • Method 4: Using Dynamic Filtering in SQL Server
• Alternate Approach to Table Name as a Variable
  • Method 1: Using Views instead of Dynamic Table Names
  • Method 2: Using a Table for Storing Table Names and Querying Dynamically
• Performance Comparison of Each Method
• Best Practices
• Real-world Examples
• Conclusion

Why do we need to use the Dynamic Table Name as a Variable in SQL Server?

Dynamic SQL is one way of utilizing a table name as a variable when dynamic queries are required across multiple tables. Such situations include querying partitioned tables, providing dynamic reports, and multi-tenant databases where table names are adaptive according to the particularities of the problem. Such tasks become automatable, given the generalization of data archived, cleaning maintenance, and schema-based queries without hardcoding table names. This is particularly useful for getting any large dataset worked upon dynamically, as it enables greater flexibility and scalability in dynamic SQL. Always use QUOTENAME() when constructing dynamic queries to prevent SQL injection.

Advantages of using Dynamic Table Name as a variable in SQL Server

• Flexibility: It enables activities on a range of tables by enabling queries to adjust to various tables without hardcoding table names.
• Scalability: It makes it easier to manage large datasets, partitioned tables, or databases with several tenants, each of which may have its schema or table.
• Decreased Code Duplication: You can reduce the requirement for many copies of almost identical code by combining comparable logic into a single query by using variables for table names.

Before getting into the methods, let us create an Orders table and insert some values into it. Consider this ‘Orders’ table as an example of the following methods.

-- Create a table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    OrderDate DATE,
    Amount DECIMAL(10, 2)
);
-- Insert values
INSERT INTO Orders (OrderID, CustomerName, OrderDate, Amount)
VALUES
(1, 'Knight', '2025-02-01', 100.50),
(2, 'Dev', '2025-02-02', 200.75),
(3, 'Kishore', '2025-02-03', 150.00),
(4, 'Sam', '2025-02-04', 250.00);
-- Display the table
Select * from orders;

This is how the table looks once it is created and inserted with the values.

Methods to use Dynamic Table Name as a Variable in SQL Server

SQL Server allows a user to use a variable for the table name; this is why the SQL statements are constructed as strings and executed through EXEC or sp_executesql. Table names are usually stored in a VARCHAR or NVARCHAR variable and then concatenated into dynamic queries using such methods.

Method 1: Using sp_executesql in SQL Server

Dynamically created SQL statements can be executed using SQL Server’s sp_executesql stored procedure.

Syntax:

EXEC sp_executesql 
    @SQLString,  -- The dynamic SQL query or statement to execute
    [ @ParamDefinition ],  -- Optional: Defines the parameters used in the dynamic SQL
    [ @ParamValue1, @ParamValue2, ... ]  -- Optional: Values of the parameters

Example:

DECLARE @TableName NVARCHAR(128) = 'Orders';
DECLARE @SQL NVARCHAR(MAX);

-- Create the dynamic SQL query
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName);

-- To Execute the dynamic SQL
EXEC sp_executesql @SQL;

Output:

Explanation: The @TableName variable name is assigned with the value ‘Orders’, as the name of the table. The table name (QUOTENAME(@TableName)) is concatenated into the @SQL variable to create the SQL query dynamically.

Method 2: Using EXEC in SQL Server

You can create SQL queries dynamically in SQL Server by using EXEC with the table name as a variable.

Syntax:

EXEC('SQLQuery');

Example:

DECLARE @TableName NVARCHAR(128) = 'Orders'; 
DECLARE @SQL NVARCHAR(MAX);

-- Create the dynamic SQL query to filter by Amount > 150
SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE Amount > 150';

-- To Execute the dynamic SQL
EXEC(@SQL);

Output:

Explanation: The table name is passed dynamically through the @TableName variable. The WHERE Statement filters out the records where Amount > 150

Method 3: Using Parameters with sp_executesql in SQL Server

You can increase security by preventing SQL injections by passing parameters using sp_executesql in SQL Server.

Syntax:

  — The dynamic SQL query or statement to execute

EXEC sp_executesql

    @SQLString,     

    @ParamDefinition, 

    @ParamValue1,     

    @ParamValue2,    

    …

Example:

DECLARE @TableName NVARCHAR(128) = 'Orders';
DECLARE @AmountThreshold DECIMAL(10, 2) = 150.00;
DECLARE @SQL NVARCHAR(MAX);

-- Create the dynamic SQL query with parameters
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE Amount > @AmountThreshold';

-- Execute the dynamic SQL with the parameter for Amount
EXEC sp_executesql @SQL, N'@AmountThreshold DECIMAL(10,2)', @AmountThreshold;

Output:

Explanation: The value for filtering orders with an amount greater than 150 is stored in the @AmountThreshold variable. The dynamic query receives this value as a parameter.

Method 4: Using Dynamic Filtering in SQL Server

With dynamic filtering, you may construct SQL queries with dynamic filter conditions that change based on user input.

Syntax:

DECLARE @SQLQuery NVARCHAR(MAX);
DECLARE @FilterCondition NVARCHAR(MAX);
DECLARE @ParamDefinition NVARCHAR(MAX);
DECLARE @Category NVARCHAR(50);

SET @Category = 'Electronics'; -- Example filter value

SET @FilterCondition = 'Category = @CategoryParam';
SET @SQLQuery = N'SELECT * FROM Products WHERE ' + @FilterCondition;
SET @ParamDefinition = N'@CategoryParam NVARCHAR(50)';

EXEC sp_executesql @SQLQuery, @ParamDefinition, @CategoryParam = @Category;

Example:

DECLARE @SQL NVARCHAR(MAX);
DECLARE @DynamicFilter NVARCHAR(100) = 'Amount > 200'; -- Example filter input

-- Construct dynamic SQL with the filter
SET @SQL = N'SELECT * FROM Orders WHERE ' + @DynamicFilter;

-- Execute the dynamic SQL
EXEC sp_executesql @SQL;

Output:

Explanation: The dynamically created filter Amount > 200 is supplied to the SELECT query’s WHERE Clause.

Alternate Approach to Table Name as a Variable

Method 1: Using Views instead of Dynamic Table Names

An alternative to dynamically creating a table name at runtime is to specify views for several tables or subsets of data.

Syntax:

CREATE VIEW [schema_name.]view_name AS
    SELECT column1, column2, ...
    FROM table_name
    [WHERE condition];

Example:

-- Step 1: Create a view for the Orders table
CREATE VIEW vw_Orders AS
SELECT OrderID, CustomerName, OrderDate, Amount
FROM Orders;
GO

-- Step 2: To Declare variables and execute dynamic SQL
DECLARE @SQL NVARCHAR(MAX);
DECLARE @FilterCondition NVARCHAR(200);
DECLARE @FilterType NVARCHAR(20) = 'CustomerName'; -- Choose between 'CustomerName' or 'OrderDate'
DECLARE @FilterValue NVARCHAR(100) = 'Dev'; -- For CustomerName filter or date like '2025-02-02'

-- Construct dynamic SQL based on filter type
IF @FilterType = 'CustomerName'
BEGIN
    SET @FilterCondition = N'WHERE CustomerName = @FilterValue';
END
ELSE IF @FilterType = 'OrderDate'
BEGIN
    SET @FilterCondition = N'WHERE OrderDate = @FilterValue';
END

-- Construct the dynamic SQL query using the view
SET @SQL = N'SELECT * FROM vw_Orders ' + @FilterCondition;

-- Execute the dynamic SQL query with the parameter
EXEC sp_executesql @SQL, N'@FilterValue NVARCHAR(100)', @FilterValue;
GO

Output:

Explanation: This query declares variables for dynamic SQL construction, after which it continues to build a query for selecting data from view vw_Orders based on that dynamic filter. The filter value is subsequently passed in as a parameter when sp_executesql runs the created SQL query.

Method 2: Using a Table for Storing Table Names and Querying Dynamically

Using SQL, this method entails building a table to hold the table name and other pertinent data, then dynamically querying these table names.

Syntax:

GO
-- Declare variables for dynamic SQL
DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(100);

-- Retrieve the table name dynamically
SELECT @TableName = TableName FROM TableNames WHERE ID = 1;

-- Construct dynamic SQL query using the table name
SET @SQL = 'SELECT * FROM ' + @TableName;

-- Execute the dynamic SQL query
EXEC sp_executesql @SQL;

Example:

GO

-- Create a table to store the table names
CREATE TABLE TableNames (
    ID INT PRIMARY KEY,
    TableName NVARCHAR(100)
);

-- Insert the Orders table name into the TableNames table
INSERT INTO TableNames (ID, TableName)
VALUES
(1, 'Orders');
GO

-- Batch 2: Declare variables for dynamic SQL
DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(100);

-- Retrieve the table name dynamically from the TableNames table
SELECT @TableName = TableName FROM TableNames WHERE ID = 1;

-- Construct the dynamic SQL query using the retrieved table name
SET @SQL = 'SELECT * FROM ' + @TableName;

-- Execute the dynamic SQL query
EXEC sp_executesql @SQL;
GO

Output:

Explanation: By keeping the table names in a metadata table, dynamic SQL makes it possible to query the orders table.

Performance Comparison of Each Method

Best Practices

• Make use of sp_executesql rather than EXEC: Sp_executesql helps with query plan caching and prevents SQL injection when it is called with parameters.
• The Prevention of SQL Injection Making use of QUOTENAME(): Table names are surrounded with QUOTENAME() to prevent input from being harmed by the query.
• Verify and limit table names: Verify user input twice at all times to ensure that only expected table names are utilized.
• For table validation, use sys.tables or a metadata table: A metadata table should have valid table names that are dynamically referenced.

Real-world Examples

1. E-Commerce Store: Every online retailer’s store has an order history table (such as Orders_StoreA, Orders_StoreB). Instead of writing several queries for each retailer, they utilize Dynamic SQL to dynamically get order information based on the chosen store.

Example:

-- Create Order Tables for Different Stores
CREATE TABLE Orders_StoreA (
    OrderID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    OrderDate DATE,
    Amount DECIMAL(10,2)
);

CREATE TABLE Orders_StoreB (
    OrderID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    OrderDate DATE,
    Amount DECIMAL(10,2)
);

-- Insert sample data into Orders_StoreA
INSERT INTO Orders_StoreA (OrderID, CustomerName, OrderDate, Amount)
VALUES 
(1, 'John', '2025-02-01', 120.50),
(2, 'Durai', '2025-02-02', 220.75);

-- Insert sample data into Orders_StoreB
INSERT INTO Orders_StoreB (OrderID, CustomerName, OrderDate, Amount)
VALUES 
(1, 'Raj', '2025-02-01', 130.25),
(2, 'David', '2025-02-02', 180.90);

DECLARE @SQL NVARCHAR(MAX);
DECLARE @StoreName NVARCHAR(50);
SET @StoreName = 'Orders_StoreA'; -- Change this to 'Orders_StoreB' for different store

-- Construct dynamic SQL query
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@StoreName);

-- Execute the query
EXEC sp_executesql @SQL;

Output:

Explanation: Here, the Orders_StoreA table, and the dynamic SQL query resolves to SELECT * FROM Orders_StoreA, fetching each record from that table. To avoid SQL injection, the table name is safely added using the QUOTENAME(@StoreName) method.

2. HR System: HR_Salary, IT_Salary, and Sales_Salary are examples of human resources (HR) systems that are used to store employee compensation across departments. The HR team wants to dynamically query employee wage records by department.

Example:

-- Create Salary Tables for Different Departments
CREATE TABLE HR_Salary (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Salary DECIMAL(10,2),
    LastUpdated DATE
);

CREATE TABLE IT_Salary (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Salary DECIMAL(10,2),
    LastUpdated DATE
);

-- Insert sample data into HR_Salary
INSERT INTO HR_Salary (EmployeeID, EmployeeName, Salary, LastUpdated)
VALUES 
(1, 'Emma', 50000.00, '2025-01-10'),
(2, 'Liam', 55000.00, '2025-02-15');

-- Insert sample data into IT_Salary
INSERT INTO IT_Salary (EmployeeID, EmployeeName, Salary, LastUpdated)
VALUES 
(1, 'Sophia', 70000.00, '2025-01-05'),
(2, 'Oliver', 75000.00, '2025-02-20');
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Department NVARCHAR(50);
SET @Department = 'IT_Salary';  -- Change to 'HR_Salary' for HR department

-- Construct dynamic SQL query
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@Department);

-- Execute the query
EXEC sp_executesql @SQL;

Output:

Explanation: Here, the table name is stored in a variable (@Department) to dynamically retrieve employee salaries from different Department tables.

Conclusion

In SQL Server, table names can be used as variables in a variety of ways, each of which provides flexibility when dynamically querying several tables. In this blog, you have gained knowledge of the use, strengths, and weaknesses of several methods, including dynamic filtering, EXEC, and sp_executesql. Several factors, such as SQL Server version, security, and query performance, will determine what is optimal. More secure and effective query execution results from following best practices, such as validating table names, simplifying execution plans, and utilizing QUOTENAME() for security.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

Dynamic Table Name as a Variable in SQL Server – FAQs