Dynamic Table Name Variable in SQL Server

In SQL Server, the dynamic table name variable is used when the name of the table is not explicitly stated in a query but is set in a variable and used instead. This can be in situations where the user does not know or the executing code does not know the table name beforehand and is only determined at run time.

Dynamic Table Variable Usage

The dynamic table variable can be used to get input from the user from the front-end at runtime or may be due to some choice from user action. By this method, the user can get data from different tables to display data in the front end based on some criteria. So the front end can send the table name dynamically from a text-box input to back-end to fetch data from different tables based on user input. There can also be other use cases for using a dynamic table name at run time.

We can create stored procedures that accept the table name as a parameter to accept different table names at run time to make the same procedure re-usable for different tables.

There can also be some situations where tables are created dynamically and data inserted at run time. Here also we can use the dynamic table name variable to handle these situations.

Examples: Dynamic Table Variable Usage

Example 1

The below example shows a very simple dynamic SQL with a table variable to get data:

Example of Dynamic Table variable using dynamic SQL.

DECLARE @tableName1 NVARCHAR(50) = 'Students';
DECLARE @SQLString NVARCHAR(2000);
SET @SQLString = N'SELECT * FROM ' + QUOTENAME(@tableName1);
EXECUTE sp_executesql @SQLString;

In the above example the '@tableName1' is used a table variable to the select statement to get table data.

Example 2

The below example shows how to use a stored procedure to get table data using parameters for table name:

Create Procedure GetDynamicTableData
(
@TableName varchar(30)
)
As
Begin
Declare @SQLString nvarchar(1000)
Set @SQLString='Select * from ' +  QUOTENAME(@TableName)
EXEC sp_executesql @SQLString
End

The @TableName variable is used in this stored procedure 'GetDynamicTableData' to send the table name to select statement.

Below is how the stored procedure is called with the table name.

EXEC GetDynamicTableData 'DynamicTab2'

Example 3

Below example shows about, how we can create a dynamic table using the table variable:

Create Procedure AddNewTable
(
@TableName varchar(30),
@ColumnName1 varchar(30),
@ColumnName2 varchar(30),
@ColumnName3 varchar(30)
)
As 
Begin
Declare @SQLString nvarchar(1000)
Set @SQLString='Create Table ' + QUOTENAME(@TableName) + '(' + QUOTENAME(@ColumnName1) + 
'varchar(50),' + QUOTENAME(@ColumnName2) + 'varchar(50),'  + QUOTENAME(@ColumnName3) + 'varchar(50))'
EXEC sp_executesql @SQLString
End

The @TableName is the variable name to pass the new table name to 'AddNewTable' stored procedure to create the new table dynamically at run time.

When we execute the below stored procedure the new table is created.

EXEC AddNewTable  'DynamicTab2','Column1','Column2','Column3'

Security Considerations

While using dynamic table names the security aspect like SQL Injection should be taken care. So always the table name in the dynamic SQL should be used with the 'QUOTENAME' like QUOTENAME(@TblName) so that no malicious command is executed and only a valid table name is used with the table variable.

Conclusion

The dynamic Table Name variable is a good method to handle table names dynamically and offers great flexibility. At the same time using dynamic table name can make the code less readable and difficulty to maintain, and so document your code in detail to make it understandable by others. Also, the dynamic table usage can lead to security issues and so care should be taken for security with proper validation.