How Can I Use Dynamic Table Names in SQL Queries?

Dynamic table name in SQL

When trying to dynamically populate a table name using a variable (such as @tablename), you may encounter an error stating that the table variable must be declared. This is because static queries (including table and column names) must remain static.

To solve this problem, consider using dynamic SQL technology. This involves dynamically generating a complete SQL statement and using sp_executesql to execute it. For example:

<code class="language-sql">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;</code>

In this example, the table name is replaced with dynamic placeholders and a complete SQL statement is generated. sp_executesql then executes this dynamic query, resolving the table names at run time.

Using dynamic SQL provides flexibility but requires attention to detail and appropriate maintenance measures. For a comprehensive guide, see "Pros and Cons of Dynamic SQL."

The above is the detailed content of How Can I Use Dynamic Table Names in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!