如何创建带可选参数的存储过程？现有写法是否为最佳实践？

你当前的写法属于通用的"全匹配"查询实现，在SQL Server 2008 R2及之后的版本中，中小数据量场景下完全可以正常使用，属于可接受的常规方案，不需要强制按照早年资料的建议拆分多分支。

不同场景的方案选择 #

• 如果你使用的是较新版本的SQL Server，且Task表数据量在十万级以内、running状态的任务占比不高，直接用你现在写的代码就行，维护成本最低，不需要额外调整。
  SQL Server从2008版本开始针对这类可选参数查询做了执行计划优化，多数场景下不会出现早年版本中常见的全表扫描、执行计划不匹配的性能问题。
• 如果表数据量达到百万级以上，且你已经在Status、UserID、AirflowDagID字段上建立了联合索引，担心参数嗅探导致性能抖动，可以优先在查询末尾加OPTION (RECOMPILE)提示，改动成本极低：

CREATE PROCEDURE [dbo].[spTask_GetRunning]
    @UserID NVARCHAR(128) NULL,
    @AirflowDagID INT NULL
    AS
BEGIN
    SELECT ID, UserID, AirflowDagID, [Name], [StartTime], [FinishedTime], [Status], [Parameter]
    FROM dbo.[Task]
    WHERE [Status] = 'running'
        AND (
            @UserID IS NULL OR
            UserID = @UserID
        )
        AND (
            @AirflowDagID IS NULL OR
            AirflowDagID = @AirflowDagID
    )
    OPTION (RECOMPILE)
END

这个提示会让存储过程每次执行时，根据当前传入的实际参数值重新生成执行计划，完全避免执行计划复用导致的索引选错问题，唯一的开销是每次执行的少量编译成本，只要这个存储过程不是每秒几十上百次的高频调用，完全可以忽略这个开销。

• 如果这个存储过程调用频率极高，不想承担每次重编译的开销，再考虑拆分独立查询分支即可。你的场景只有2个可选参数，总共4种参数组合，拆分后的代码维护成本也很低，每个分支的查询条件完全确定，优化器可以稳定生成匹配索引的最优执行计划。

早年资料里提到"必须拆分多分支才能获得好性能"的结论，是基于SQL Server 2005及更早版本的优化器能力得出的，在新版本中已经不是强制要求，不需要盲目照搬十几年前的经验。

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