How to set table name in dynamic SQL query?
Table names cannot be supplied as parameters, so you'll have to construct the SQL string manually like this:
SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID'
However, make sure that your application does not allow a user to directly enter the value of @TableName
, as this would make your query susceptible to SQL injection. For one possible solution to this, see this answer.
Use dynamic table name in sql server query
you need to use Dynamic SQL
either
declare @sql nvarchar(max)
select @sql = 'select * from ' + quotename(@tableName)
exec (@sql)
or
exec sp_executesql @sql
Dynamic table name and variable name in query in SQL Server
Use sp_executesql
and parameters:
DECLARE @table_name VARCHAR(50) = 'table_name';
DECLARE @valid_to datetime = getdate();
DECLARE @sql NVARCHAR(max) = N'
UPDATE '+ @table_name + N'
SET valid_flag = 0,
valid_to = @valid_to
WHERE valid_flag = 1
';
EXEC sp_executesql @sql, N'@valid_to datetime', @valid_to=@valid_to;
EDIT:
As recommended by Larnu a comment:
DECLARE @table_name sysname = 'table_name';
DECLARE @valid_to datetime = getdate();
DECLARE @sql NVARCHAR(max) = N'
UPDATE '+ QUOTENAME(@table_name) + N'
SET valid_flag = 0,
valid_to = @valid_to
WHERE valid_flag = 1
';
EXEC sp_executesql @sql, N'@valid_to datetime', @valid_to=@valid_to;
How to provide dynamic table name with Date Time in query of SQL Server
It can be resolved by below query
Declare @tablename AS nvarchar(80);
SET @tablename='TableNameBackup_'+
replace(replace(replace(convert(varchar(30),getdate(),121),'/','_'),':','_'),'.','_');
EXECUTE('Select * into '+ @tablename+ ' from TableName');
A table name as a variable
For static queries, like the one in your question, table names and column names need to be static.
For dynamic queries, you should generate the full SQL dynamically, and use sp_executesql to execute it.
Here is an example of a script used to compare data between the same tables of different databases:
Static query:
SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]
Since I want to easily change the name of table
and schema
, I have created this dynamic query:
declare @schema sysname;
declare @table sysname;
declare @query nvarchar(max);
set @schema = 'dbo'
set @table = 'ACTY'
set @query = '
SELECT * FROM [DB_ONE].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
EXCEPT
SELECT * FROM [DB_TWO].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);
EXEC sp_executesql @query
Since dynamic queries have many details that need to be considered and they are hard to maintain, I recommend that you read: The curse and blessings of dynamic SQL
Related Topics
How to Partition Postgres Table Using Intermediate Table
Select Last Records from Table Using Group By
How to Display Number Value in Words
Oracle - Select Count on a Subquery
What Are Some of Your Most Useful Database Standards
Setting Variables in SQL Functions/Probs
Why Partitions Elimination Does Not Happen for This Query
SQL Server: Order by Parameters in In Statement
Insert Inserted Id to Another Table
Passing Multiple Values in Single Parameter
"Rolling Up" Groups in Jaspersoft Ireport
Postgresql 9.4 - Prevent App Selecting Always the Latest Updated Rows
Oracle Shutdown Error Ora-01033
SQL Server Cte Parent Child Recursive
How to Remove Certain Results from My Search Count
Oracle SQL: Variables Used in Place of Table Names
Cs50 Pset 7 13.Sql, I Can't Solve It, Nested SQLite3 Database