CODEWITHSUNDEEP
sqlt-sqlstored-procedures
Jean-François Beaulieu
Jean-François Beaulieu

Reputation: 4455

SELECT FROM depending on a parameter in SQL

I want to obtain from a parameter, @Display the value 0, 1 or 2. When @Display = 0, I want to display all the items for which ec.IsEquipmentRelated is true. When @Display = 0, I want to display all the items for which ec.IsEquipmentRelated is false and when @Display = 2, I want to display all the items for which ec.IsEquipmentRelated is true OR false. How can I implement this in the FROM section?

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 ...

Upvotes: 0

Views: 121

Answers (2)

Narnian
Narnian

Reputation: 3908

This should work:

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) )

Upvotes: 1

JNK
JNK

Reputation: 65217

Do it in a WHERE clause:

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

Upvotes: 1

Related Questions