Programming Tutorials

Use a dynamic table name in a SQL Server SELECT statement

By: Gren in MySQL Tutorials on 2023-05-26  

To use a dynamic table name in a SQL Server SELECT statement, you can utilize dynamic SQL using the EXEC or sp_executesql statement. Here's an example of how you can achieve it:

DECLARE @tableName NVARCHAR(50)
DECLARE @sql NVARCHAR(MAX)

-- Get the dynamic table name
SET @tableName = 'PM' + CAST((SELECT MAX(col1) FROM table1) AS NVARCHAR(50))

-- Construct the dynamic SQL statement
SET @sql = 'SELECT * FROM ' + QUOTENAME(@tableName)

-- Execute the dynamic SQL
EXEC sp_executesql @sql

In the above example, @tableName is the variable that holds the dynamic table name. You can set it by concatenating the 'PM' prefix with the value retrieved from the table1 using MAX(col1).

The dynamic SQL statement is constructed by concatenating the 'SELECT * FROM ' with the dynamic table name using QUOTENAME function to properly handle any special characters in the table name.

Finally, the dynamic SQL statement is executed using sp_executesql to retrieve the data from the dynamic table.

Make sure to adjust the table and column names to match your actual schema. Additionally, keep in mind that dynamic SQL can introduce security risks like SQL injection, so it's important to validate and sanitize the input appropriately.






Add Comment

* Required information
1000

Comments

No comments yet. Be the first!

Most Viewed Articles (in MySQL )

Use a dynamic table name in a SQL Server SELECT statement

mysqldumpslow in MySQL - Summarize slow query log.

Inserting Data into Tables in MySQL

Windows cannot access the specified device, path or file. You may not have the appropriate permissions to access them.

sql if null then 0

Finding slow queries in MySQL - Enable slow query log.

Table __________ is marked as crashed and should be repaired.

Modifying data and using WHERE clause in MySQL

Querying the Database in MySQL

ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Modify a auto_increment id column in mysql to accept a 5 digit random number instead

Sample my.cnf (my.ini) for MySQL with 1GB RAM

Changing the Structure of an Existing Table in MySQL

What is SQL Injection

Deleting Data in MySQL

Latest Articles (in MySQL)