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'
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.