如何用单条T-SQL语句实现带参数的条件查询与动态排序

变量声明 #

DECLARE @FirstChar VarChar(1) = '*'
DECLARE @SortOrder VarChar(1) = 'C'

原查询逻辑 #

查询1（返回所有数据） #

SELECT
    aCode,
    aName,
    aAddress,
    aPhone
FROM 
    [Agency]
ORDER BY 
    CASE 
        WHEN @SortOrder = 'C' THEN aCode
        WHEN @SortOrder = 'N' THEN aName
    END

查询2（按首字符过滤数据） #

SELECT
    aCode,
    aName,
    aAddress,
    aPhone
FROM 
    [Agency]
WHERE 
    @SortOrder = 'C' 
    AND SUBSTRING(LTRIM(RTRIM(aCode)), 1, 1) LIKE '%' + @FirstChar
ORDER BY 
    CASE 
        WHEN @SortOrder = 'C' THEN aCode
        WHEN @SortOrder = 'N' THEN aName
    END

需求说明 #

当@FirstChar = "*"时，需返回所有数据行（即执行查询1）；当@FirstChar <> '*'时，仅返回匹配参数值的数据行（即执行查询2）。具体场景：

• 若@SortOrder = "C"且@FirstChar = "*"，返回所有行并按aCode排序
• 若@SortOrder = "N"且@FirstChar = "*"，返回所有行并按aName排序

问题 #

如何不依赖上述两条查询，仅用单条T-SQL语句实现上述需求？

解决方案 #

以下是合并后的可行查询（感谢FAB提供思路）：

SELECT
    aCode,
    aName,
    aAddress,
    aPhone
FROM [Agency] 
WHERE
    CASE
        WHEN @FirstChar = '*' THEN 1
    END = 1
    OR 
    CASE 
        WHEN @SortOrder = 'C' THEN SUBSTRING(LTRIM(RTRIM(aCode)), 1, 1)
        ELSE SUBSTRING(LTRIM(RTRIM(aName)), 1, 1)
    END = @FirstChar
ORDER BY 
    CASE 
        WHEN @SortOrder = 'C' THEN aCode
        WHEN @SortOrder = 'N' THEN aName
    END

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