tSELECT FROM取决于SQL中的参数

Question

我想从参数@Display中获得值0、1或2。当@Display=0时，我想显示ec.IsEquipmentRelated为true的所有项目。当@Display=0时，我希望显示ec.IsEquipmentRelated为false的所有项目；当@Display=2时，我想要显示ec.IsEquipmentRelated为true的所有项目ORfalse。如何在FROM部分中实现此功能？

ALTER PROCEDURE [dbo].[Downtime_GetNewCause_EquimentLocation]
    @DisplayInactive bit = 0,
    @SortOrder INT = 0,
    @Diplay INT = 0
AS
-- Main Data source
SELECT    ic.IncidentCauseID
        , 'EquimentRelated' = COALESCE(ec.IsEquipmentRelated, 0)
        , ic.NewIncidentCauseID
        , ic.DisplayName 
        , ic.IsLegacy
        , el.EquipmentLocationID
        , el.ShopID
        , ec.EquipmentClassID
        , ec.EquipmentAbbr
        , el.ClassSequenceNumber
        , el.EquipmentComponent
        , el.CompSequenceNumber
        , ic.IsActive
FROM    Downtime_IncidentCauses ic
        LEFT JOIN Downtime_EquipmentLocations el ON ic.EquipmentLocationID = el.EquipmentLocationID
        LEFT JOIN Downtime_EquipmentClasses ec ON el.EquipmentClassID = ec.EquipmentClassID AND
            CASE WHEN @Diplay = 0 THEN ...
            CASE WHEN @Diplay = 1 THEN ...
            CASE WHEN @Diplay = 2 THEN ...

Answer 1

这应该有效：

INNER JOIN Downtime_EquipmentClasses ec 
ON (el.EquipmentClassID = ec.EquipmentClassID)
    AND (  (@Display = 0 AND ec.IsEquipmentRelated = 1) 
        OR (@Display = 1 AND ec.IsEquipmentRelated = 0) 
        OR (@Display = 2) )

Answer 2

在WHERE子句中执行：

WHERE (@Display = 0 AND ec.IsEquipmentRelated = 'True')
OR (@Display = 1 AND ec.IsEquipmentRelated = 'False')
OR @Display = 2