How Can I Dynamically Determine a Table Name in SQL Queries?

Flexible use of table names: Tips for dynamic SQL

Using variables directly as table names in static SQL statements, although it seems convenient, is prone to errors, as shown in the example. To avoid such problems, the following methods are recommended:

Static query

In static queries, table and column names should remain unchanged.

Dynamic query

Dynamic queries allow query strings to be generated programmatically, allowing for flexibility. Here’s how:

1. Dynamicly generate complete SQL query statements, including table names and column names.
2. Use sp_executesql to execute the generated query.

Example

The following script compares data from matching tables in different databases:

Static query:

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

Dynamic query:

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

Note:

Dynamic SQL requires careful design and maintenance. Please refer to the relevant documentation, such as Analysis of the Advantages and Disadvantages of Dynamic SQL, for more comprehensive guidance.

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