传递参数时过滤a，不传递参数时返回all

Question

如何在传递参数时过滤查询中的记录，并在参数为空值时返回all。

SELECT nfv.Company_id, COUNT(1) as NegativeFeedback,SUM(allocated_score) as ScoreSum
FROM dbo.NegativeFeedbackView nfv
WHERE ISNULL(nfv.comp_nature_id,0) = @compNatureId 
GROUP BY nfv.Company_id

Answer 1

使用IF/ELSE

例如：

IF @compNatureId IS NULL
BEGIN

    SELECT nfv.Company_id, COUNT(1) as NegativeFeedback,SUM(allocated_score) as ScoreSum
    FROM dbo.NegativeFeedbackView nfv
    WHERE ISNULL(nfv.comp_nature_id,0) = @compNatureId 
    GROUP BY nfv.Company_id

END
ELSE
BEGIN

    SELECT nfv.Company_id, COUNT(1) as NegativeFeedback,SUM(allocated_score) as ScoreSum
    FROM dbo.NegativeFeedbackView nfv
    GROUP BY nfv.Company_id

END

尝试将这些情况组合到一个查询中：

SELECT nfv.Company_id, COUNT(1) as NegativeFeedback,SUM(allocated_score) as ScoreSum
FROM dbo.NegativeFeedbackView nfv
WHERE ISNULL(nfv.comp_nature_id,0) = @compNatureId 
OR @compNatureId IS NULL
GROUP BY nfv.Company_id

可能会产生一个次优的计划，并且它不能在comp_nature_id上使用任何索引(如果存在)。

Answer 2

下面是一个使用可选参数进行过滤的示例proc。

CREATE PROCEDURE USP_MyProc(@FilterParameter AS VARCHAR(100) = NULL)

BEGIN

SELECT nfv.Company_id, COUNT(1) as NegativeFeedback,SUM(allocated_score) as ScoreSum
FROM dbo.NegativeFeedbackView nfv

WHERE
(@compNatureId  IS NULL OR ISNULL(nfv.comp_nature_id,0)= @compNatureId )

GROUP BY nfv.Company_id

END