SQL Server报错：'@column_name'附近语法不正确，动态列查询如何解决？

Hey there! I get why this is frustrating—you can’t directly use a variable as a column name in a static SQL query like you tried. SQL Server treats @column_name as a literal value here, not as a reference to an actual column in your tables. Here’s how to fix this properly:

The Solution: Dynamic SQL #

You need to construct your SQL query as a string dynamically, then execute it. Using sp_executesql is the safest way to do this (it helps prevent SQL injection issues compared to plain EXEC).

Here’s the corrected version of your code:

DECLARE @column_name varchar(50) 
SET @column_name = 'test'
DECLARE @sql nvarchar(max)

-- Build the dynamic SQL string, using QUOTENAME to safely wrap the column name
SET @sql = N'SELECT t1.' + QUOTENAME(@column_name) + N', t2.' + QUOTENAME(@column_name) + N' 
FROM table_test AS t1 
INNER JOIN table_test2 AS t2 ON t2.id = t1.id'

-- Execute the dynamic query
EXEC sp_executesql @sql

Why This Works #

• QUOTENAME() is crucial here: it wraps your column name in square brackets (e.g., turns test into [test]), which handles cases where your column name has special characters, spaces, or matches a SQL keyword. It also protects against SQL injection if @column_name ever comes from user input.
• By building the query as a string, you’re telling SQL Server to interpret @column_name as the actual column name when it executes the dynamic SQL.

A Quick Note on SQL Injection #

If @column_name isn’t hardcoded (e.g., it comes from a user input), always validate the value first (make sure it’s a valid column name in your tables) and use QUOTENAME() to sanitize it. This prevents attackers from inserting malicious SQL into your query.

内容的提问来源于stack exchange，提问作者Chuah Cheng Jun