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:


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


No comments yet. Be the first!

Most Viewed Articles (in MySQL )

Latest Articles (in MySQL)