SQL Server动态CASE语句问题求助：IN子句处理多值参数失败

Hey there! Let's break down why your current query isn't working for comma-separated @id values, then walk through a few straightforward solutions that'll get you up and running.

The Root of the Problem #

Your original query uses CASE WHEN to return either the entire test.id column or the @id string. When you pass a multi-value string like '123,124,125', the CASE statement returns that full string as a single value. So your query ends up running:

SELECT * FROM test WHERE test.id IN ('123,124,125')

This is looking for records where id equals the entire comma-separated string (not the individual values), which is why it fails.

Solution 1: Use STRING_SPLIT (Recommended for SQL Server 2016+) #

SQL Server 2016 introduced STRING_SPLIT, which turns a comma-separated string into a table of individual values. This is clean and efficient:

SELECT * 
FROM test 
WHERE 
    -- Return all records if @id is empty
    @id = '' 
    -- Otherwise match against split values
    OR test.id IN (SELECT value FROM STRING_SPLIT(@id, ','))

Pro tip: If your id column is a string type instead of numeric, make sure to cast the split values to match (e.g., CAST(value AS VARCHAR(50))).

Solution 2: Dynamic SQL (For Older SQL Server Versions) #

If you're stuck on a version before 2016, dynamic SQL lets you build the query string on the fly. Just be careful to validate input to avoid SQL injection risks:

DECLARE @sql NVARCHAR(MAX)

-- Build the base query
SET @sql = 'SELECT * FROM test '

-- Add the WHERE clause only if @id isn't empty
IF @id <> ''
    SET @sql = @sql + 'WHERE test.id IN (' + @id + ')'

-- Execute the final query
EXEC sp_executesql @sql

Important: Only use this if you control the @id input, or add validation to ensure it only contains numbers and commas (e.g., check that @id matches ^[0-9,]+$ with a regex).

Solution 3: XML-Based String Splitting (Compatible with SQL Server 2005+) #

If you can't use STRING_SPLIT and want to avoid dynamic SQL, you can use XML to parse the comma-separated string:

SELECT * 
FROM test
WHERE
    @id = '' 
    OR test.id IN (
        SELECT x.value('.', 'INT') AS id_value
        FROM (
            -- Convert the string to an XML fragment
            SELECT CAST('<ids>' + REPLACE(@id, ',', '</ids><ids>') + '</ids>' AS XML) AS xml_ids
        ) AS t
        -- Extract each <ids> node value
        CROSS APPLY xml_ids.nodes('/ids') AS n(x)
    )

This converts your comma-separated string into XML elements, then extracts each value to use in the IN clause.

Quick Notes #

• Always clean up your @id input: Remove leading/trailing commas (e.g., SET @id = TRIM(',', @id) for SQL Server 2017+, or SET @id = LTRIM(RTRIM(REPLACE(@id, ',,', ','))) for older versions) to avoid empty values in the split results.
• Test with both empty @id and multi-value inputs to make sure all cases work as expected.

内容的提问来源于stack exchange，提问作者Lucifer