如何用DECLARE变量作为表列名在WHERE子句中使用？最佳实践

Got it, let's break down why your current query isn't working first. When you use @ProductLines directly in the WHERE clause like (@ProductLines Like '%' + @searchProductlines + '%'), SQL treats @ProductLines as a literal string (the value 'usr_author') instead of referencing the actual column. So you're comparing the string 'usr_author' to '%hc%'—that's why it never returns the results you want.

Here are the best practices for your scenario, depending on your needs:

1. Parameterized Dynamic SQL (Best for Flexible/Scalable Column Selection) #

If you need to support any column from the Products table (or a growing list of columns), this is the way to go. It fixes the literal string issue while keeping your query safe from SQL injection (unlike raw EXEC('code') which can be risky if not handled properly).

The key here is using sp_executesql for parameterization, QUOTENAME() to safely escape column names, and validating that the column exists before executing:

DECLARE @ProductLines nvarchar(50) = 'usr_author'
DECLARE @searchProductlines nvarchar(50) = 'hc'
DECLARE @sql nvarchar(MAX)

-- First, validate the column exists to prevent invalid references and injection
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME = 'Products' AND COLUMN_NAME = @ProductLines)
BEGIN
    -- Build the dynamic SQL with safe column escaping
    SET @sql = N'SELECT TOP 20 
                    Productid as Produktid, 
                    usr_Author AS Author, 
                    Header AS Title, 
                    usr_Publisher AS Publisher, 
                    CustomerId AS Customerid 
                FROM Products 
                WHERE ' + QUOTENAME(@ProductLines) + N' LIKE ''%'' + @search + ''%'''

    -- Execute with parameterized search value to avoid injection and handle data types correctly
    EXEC sp_executesql @sql, N'@search nvarchar(50)', @search = @searchProductlines
END
ELSE
BEGIN
    -- Throw an error if the column doesn't exist
    RAISERROR('Invalid column name specified in @ProductLines', 16, 1)
END

This approach also solves your GETDATE() issue from earlier—if you need to include date logic, you can write it directly in the dynamic SQL string (e.g., WHERE DateColumn >= GETDATE()) instead of trying to concatenate it as a string.

2. CASE WHEN (Best for Fixed, Limited Column Options) #

If your dropdown menu only includes a small, fixed set of columns (like usr_author, usr_Publisher, and Header), a concise CASE WHEN avoids dynamic SQL entirely. It's simpler and has no injection risk:

DECLARE @ProductLines nvarchar(50) = 'usr_author'
DECLARE @searchProductlines nvarchar(50) = 'hc'

SELECT TOP 20 
    Productid as Produktid, 
    usr_Author AS Author, 
    Header AS Title, 
    usr_Publisher AS Publisher, 
    CustomerId AS Customerid 
FROM Products 
WHERE 
    CASE @ProductLines
        WHEN 'usr_author' THEN usr_Author
        WHEN 'usr_Publisher' THEN usr_Publisher
        WHEN 'Header' THEN Header
        -- Add more columns here as needed
        ELSE NULL -- Return no matches if invalid column is passed
    END LIKE '%' + @searchProductlines + '%'

3. CHOOSE (Simpler Alternative for Fixed Columns, SQL Server 2012+) #

If you're on SQL Server 2012 or later, CHOOSE can make the syntax a bit cleaner for fixed columns. You just map your column names to an index:

DECLARE @ProductLines nvarchar(50) = 'usr_author'
DECLARE @searchProductlines nvarchar(50) = 'hc'

-- Map column name to an index for CHOOSE
DECLARE @colIndex int = CASE @ProductLines
                            WHEN 'usr_author' THEN 1
                            WHEN 'usr_Publisher' THEN 2
                            WHEN 'Header' THEN 3
                        END

SELECT TOP 20 
    Productid as Produktid, 
    usr_Author AS Author, 
    Header AS Title, 
    usr_Publisher AS Publisher, 
    CustomerId AS Customerid 
FROM Products 
WHERE CHOOSE(@colIndex, usr_Author, usr_Publisher, Header) LIKE '%' + @searchProductlines + '%'

Which Should You Pick? #

• Go with parameterized dynamic SQL if you need flexibility (supporting any column or future columns). It's the most scalable and safe when implemented correctly.
• Use CASE WHEN/CHOOSE if your column options are fixed and limited. It's simpler and avoids the complexity of dynamic SQL.

内容的提问来源于stack exchange，提问作者Mark Mazur Lussenburg