How Can I Use a Table Name Variable in a SQL Query?

Use variables as table names

Question:

When trying to execute a query using a table name stored in a variable (such as "@tablename"), you receive an error stating that the table variable "@tablename" must be declared.

Solution:

In a static query like the one shown in the question, the table and column names must be static. For dynamic queries with dynamically populated table names, the complete SQL must be dynamically generated and executed using sp_executesql.

Example:

Suppose we want to compare data from the same table in different databases. A static query looks like this:

<code class="language-sql">SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]</code>

To make this query dynamic, the schema and table name can be set as variables:

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

By using sp_executesql, dynamically generated SQL can be executed.

Other notes:

Dynamic queries require careful consideration and maintenance. It is recommended to familiarize yourself with the details of dynamic SQL before implementing such queries in your code.

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