How to Set Table Name in Dynamic SQL Query

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;

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

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');

Dynamic SQL with table name as a parameter

You can only use bind variables (denoted by colons) for values, not for parts of the structure. You will have to concatenate the table and column names into the query:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where REF_D = ' || column2 into ATTR_AVG;

Which implies REF_D is a fixed column name that can appear in any table you'll call this for; in a previous question that seems to be a variable. If it is actually a string variable then you'd need to bind and set that:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where ' || column2 || ' = :REF_D' into ATTR_AVG using REF_D;

If it's supposed to be a date you should make sure the local variable is the right type, or explicitly convert it.

Is a cursor or second stored procedure required for a set of dynamic SQL statements with dynamic table names

You could include identity column in your temp table for the alternative way of CURSOR. modified little based on your query as below:

create table #mytablename (app_name varchar(255), count_loop int identity(1,1))
insert into #mytablename
select s.name from sysobjects s where s.type='U'

declare @max int, @current int
declare @my_app nvarchar(255)
declare @sql nvarchar(max)

set @current=1
select @max = MAX(count_loop) from #mytablename 

while @current < @max 
begin
    
    select @my_app = app_name from #mytablename where count_loop = @current

    select @sql = 'select trim(substring(left(app_name,len(app_name)-7),10,100)) from ' + @my_app
    exec(@sql)

    set @current=@current+1

end

---

Related Topics