写存储过程的时候，排序需求往往是多变的：同一个列表页，有的用户按创建时间看，有的按金额看，还有的要按状态优先级排。如果给每种排序各写一套SQL，维护成本会直线上升。动态排序就是为了解决这个问题而生的——用一个排序参数控制ORDER BY的行为，再配合可选的过滤条件，一套存储过程就能覆盖绝大多数列表查询场景。下面结合实际代码，把几种主流实现方式和踩过的坑讲清楚。
方案一：用CASE WHEN实现静态SQL内的动态排序
最直接的办法是不拼字符串，直接在ORDER BY里用CASE WHEN根据参数分支。这种写法的好处是SQL语句本身是静态的，执行计划可以被缓存复用，也不存在SQL注入风险，是安全性最高的方案。它的原理很简单：CASE表达式会根据传入的排序参数返回不同的列值，数据库引擎按这个返回值排序。
CREATE PROCEDURE usp_GetOrders
@FilterStatus INT = NULL,
@SortField NVARCHAR(20) = 'CreateTime',
@SortDir NVARCHAR(4) = 'ASC'
AS
BEGIN
SELECT OrderId, CustomerName, Amount, Status, CreateTime
FROM Orders
WHERE (@FilterStatus IS NULL OR Status = @FilterStatus)
ORDER BY
CASE WHEN @SortField = 'Amount' THEN Amount END DESC,
CASE WHEN @SortField = 'CreateTime' THEN CreateTime END ASC,
CASE WHEN @SortField = 'CustomerName' THEN CustomerName END ASC;
END
注意上面代码里每个CASE分支单独占一个排序位，没有写ELSE。如果写成CASE WHEN @SortField = 'Amount' THEN Amount ELSE CreateTime END这种混合类型的写法，当不同分支返回的类型不一致时（比如金额是DECIMAL、时间是DATETIME），SQL Server会直接报类型转换错误。拆成多个排序位、不给ELSE，不匹配的分支返回NULL，NULL在排序中会被排到一起，但因为只有一个分支有值，实际效果就是按指定字段排。
这种方案有个明显局限：排序方向不好动态控制。CASE表达式只能控制排什么，控制不了ASC还是DESC，因为CASE返回的是值而不是关键字。常见变通办法是把方向反过来算，比如降序排金额可以改成按负数排：CASE WHEN @SortDir = 'DESC' THEN -Amount ELSE Amount END。这个技巧只对数值类型有效，日期和字符串就没办法了，需要方向组合多的时候还是得靠动态SQL。
方案二：sp_executesql拼接动态SQL
当排序字段和方向组合较多，或者排序逻辑复杂（比如多字段排序、带表达式排序）时，拼接动态SQL是更灵活的选择。核心思路是：过滤条件仍然用参数化传递保证安全，只有ORDER BY子句这一小段根据白名单校验后拼接进去。这样既保留了参数化查询的注入防护，又获得了完整排序灵活性。
CREATE PROCEDURE usp_GetOrdersDynamic
@FilterStatus INT = NULL,
@MinAmount DECIMAL(18,2) = NULL,
@SortField NVARCHAR(20) = 'CreateTime',
@SortDir NVARCHAR(4) = 'ASC',
@PageIndex INT = 1,
@PageSize INT = 20
AS
BEGIN
-- 白名单校验，防止SQL注入
DECLARE @allowedFields TABLE (f NVARCHAR(20));
INSERT INTO @allowedFields VALUES ('CreateTime'), ('Amount'), ('CustomerName');
IF NOT EXISTS (SELECT 1 FROM @allowedFields WHERE f = @SortField)
SET @SortField = 'CreateTime';
IF @SortDir NOT IN ('ASC', 'DESC')
SET @SortDir = 'ASC';
DECLARE @sql NVARCHAR(MAX), @offset INT = (@PageIndex - 1) * @PageSize;
SET @sql = N'
SELECT OrderId, CustomerName, Amount, Status, CreateTime
FROM Orders
WHERE (@FilterStatus IS NULL OR Status = @FilterStatus)
AND (@MinAmount IS NULL OR Amount >= @MinAmount)
ORDER BY ' + QUOTENAME(@SortField) + N' ' + @SortDir +
N' OFFSET @offset ROWS FETCH NEXT @pagesize ROWS ONLY;';
EXEC sp_executesql @sql,
N'@FilterStatus INT, @MinAmount DECIMAL(18,2), @offset INT, @pagesize INT',
@FilterStatus, @MinAmount, @offset, @PageSize;
END
这里有三道安全防线值得强调。第一道是白名单校验，传入的排序字段必须在允许的集合内，任何不认识的值都会回退到默认字段，这从源头堵住了注入。第二道是QUOTENAME函数，它会给字段名加上方括号并转义内部的中括号，即使有人构造了带特殊字符的字段名也无法逃逸。第三道是过滤条件全部走参数化，值永远不会被拼进SQL字符串。三道防线缺一不可，只靠其中一道都有被绕过的可能。
动态SQL的代价是参数嗅探和执行计划缓存问题。排序字段不同，最优的索引也不同，但sp_executesql生成的执行计划会按SQL文本缓存，同一个文本可能命中不适配当前排序参数的计划。如果查询并发量大且性能敏感，可以考虑给不同排序字段拼出不同的SQL文本（比如在SQL里加一个注释标记排序字段），让每种排序各自缓存执行计划。
方案三：ROW_NUMBER预排序配合参数过滤
在老版本数据库不支持OFFSET FETCH语法的场景下，或者需要在排序基础上做分组取Top N（比如每个客户取最新一单）时，ROW_NUMBER是主力工具。它先在子查询里完成动态排序并编号，外层再按编号过滤，逻辑清晰且扩展性强。
CREATE PROCEDURE usp_GetOrdersByRowNumber
@FilterStatus INT = NULL,
@SortField NVARCHAR(20) = 'CreateTime',
@PageIndex INT = 1,
@PageSize INT = 20
AS
BEGIN
DECLARE @sortExpr NVARCHAR(200);
SET @sortExpr = CASE @SortField
WHEN 'Amount' THEN 'Amount DESC'
WHEN 'CustomerName' THEN 'CustomerName ASC'
ELSE 'CreateTime DESC' END;
DECLARE @sql NVARCHAR(MAX), @start INT = (@PageIndex - 1) * @PageSize + 1;
SET @sql = N'
SELECT OrderId, CustomerName, Amount, CreateTime
FROM (
SELECT OrderId, CustomerName, Amount, CreateTime,
ROW_NUMBER() OVER (ORDER BY ' + @sortExpr + N') AS rn
FROM Orders
WHERE (@FilterStatus IS NULL OR Status = @FilterStatus)
) t
WHERE rn BETWEEN @start AND @start + @pagesize - 1;';
EXEC sp_executesql @sql, N'@FilterStatus INT, @start INT, @pagesize INT',
@FilterStatus, @start, @PageSize;
END
这个方案里排序表达式是在存储过程内部通过CASE映射生成的，外部传入的只是字段代号，天然免疫注入。ROW_NUMBER的另一个优势是容易改成DENSE_RANK或NTILE实现分组分页、随机分组等更复杂的需求。缺点是多了一层子查询，大数据量下如果没有匹配排序字段的索引，排序开销会集中在整个结果集上，分页越靠后越慢。配合覆盖索引（把排序列和查询列都放进索引）能显著缓解这个问题。
过滤条件与排序逻辑的组合优化建议
动态排序要跑得快，关键不在写法而在索引设计。每种可能的排序字段都应该有对应的索引支撑，并且索引列顺序要与过滤列加排序列匹配。比如常用组合是按状态过滤再按创建时间倒序，那么索引应该是(Status, CreateTime DESC)。可以在测试环境用SET STATISTICS IO ON对比不同排序参数下的逻辑读次数，找出缺失索引的组合。
另一个容易被忽略的点是可选过滤条件的写法。WHERE (@FilterStatus IS NULL OR Status = @FilterStatus)这种写法虽然通用，但会抑制索引查找，因为优化器难以针对OR条件生成高效计划。更好的做法是动态拼接WHERE子句，参数仍然参数化传递，只在参数非空时才追加对应条件。这样每种过滤组合都有独立的SQL文本和执行计划，长期来看性能更稳定。
最后总结一下选型思路：排序字段少、类型兼容就用CASE WHEN，简单安全；排序组合多变、需要复杂表达式就用动态SQL加白名单；老版本数据库或需要编号做二次逻辑就用ROW_NUMBER。无论哪种方案，排序字段白名单校验都是不可省略的一环，灵活性永远不能以牺牲安全性为代价。