SQL存储过程可选参数问题：计算字段未识别及空参数需求

修改后的存储过程（CTE版本，可读性优先） #

CREATE PROCEDURE [dbo].[Chargeable_Time] 
    @DateFrom  DATE,
    @DateTo    DATE,
    @Allocated INT = NULL
AS
WITH TimesheetWithAllocated AS (
    SELECT 
        t.CaseID,
        c.DisplayNo,
        c.CaseName,
        c.[Category],
        t.[Date],
        t.FeeEarner,
        u.Username,
        t.ChargeCode,
        t.[Hours],
        t.Fees,
        t.GroupID,
        CASE WHEN t.GroupID IS NOT NULL THEN 1 ELSE 0 END AS Allocated
    FROM Timesheet AS t
    LEFT JOIN [User] AS u ON t.FeeEarner = u.ID
    LEFT JOIN [Case] AS c ON t.CaseID = c.ID
    WHERE 
        t.Active = 'True'
        AND t.CaseID IS NOT NULL
        AND t.Rate > 0
        AND t.[Date] >= @DateFrom
        AND t.[Date] <= @DateTo
)
SELECT *
FROM TimesheetWithAllocated
WHERE 
    (@Allocated IS NULL OR Allocated = @Allocated)

另一种写法（直接在WHERE中复用CASE，性能略优） #

CREATE PROCEDURE [dbo].[Chargeable_Time] 
    @DateFrom  DATE,
    @DateTo    DATE,
    @Allocated INT = NULL
AS
SELECT 
    t.CaseID,
    c.DisplayNo,
    c.CaseName,
    c.[Category],
    t.[Date],
    t.FeeEarner,
    u.Username,
    t.ChargeCode,
    t.[Hours],
    t.Fees,
    t.GroupID,
    CASE WHEN t.GroupID IS NOT NULL THEN 1 ELSE 0 END AS Allocated
FROM Timesheet AS t
LEFT JOIN [User] AS u ON t.FeeEarner = u.ID
LEFT JOIN [Case] AS c ON t.CaseID = c.ID
WHERE 
    t.Active = 'True'
    AND t.CaseID IS NOT NULL
    AND t.Rate > 0
    AND t.[Date] >= @DateFrom
    AND t.[Date] <= @DateTo
    AND (@Allocated IS NULL OR CASE WHEN t.GroupID IS NOT NULL THEN 1 ELSE 0 END = @Allocated)

问题解决说明 #

• 计算字段无法在WHERE中识别：SQL执行顺序是先处理WHERE过滤，再生成SELECT中的字段别名，因此WHERE里不能直接引用SELECT定义的别名。两种解决方案：

  1. 用CTE（公共表表达式）先计算出Allocated字段，再在后续查询中引用这个别名
  2. 在WHERE子句中重复CASE表达式，直接基于原始字段GroupID判断状态

• 参数为空时返回全部数据：通过@Allocated IS NULL OR Allocated = @Allocated的条件实现：

  • 当@Allocated为NULL时，@Allocated IS NULL成立，整个条件为真，不会过滤任何数据
  • 当传入0或1时，仅保留Allocated值匹配的记录

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