CODEWITHSUNDEEP
sql-serverwhere-clause
user3688946
user3688946

Reputation:

Variable name as Column in Sql Server 2012 WHERE Clause

I've tried a variety of methods, and this is one I was sure would work -- but the alter storedprocedure fails at the CASE line.

     WHERE v.State_combined != ''

    CASE WHEN @component = 'Component 1' 
        THEN
        AND (v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')) 
        WHEN @component = 'Component 2' 
        THEN
        AND (v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED'))
        ELSE
        AND (v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED') or       v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED'))
        END

    AND (@state IS null OR v.STATE_COMBINED = @state)

Upvotes: 0

Views: 50

Answers (2)

Pred
Pred

Reputation: 9042

Example solution with CASE..WHEN

WHERE
    v.State_combined != ''
    AND 1 = CASE
        WHEN @component = 'Component 1' AND (v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')) THEN 1
        WHEN @component = 'Component 2' AND (v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')) THEN 1
        WHEN (
            v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')
            OR v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')
        ) THEN 1
        ELSE 0
END
AND (@state IS null OR v.STATE_COMBINED = @state)

The CASE..WHEN will return a value, you can not use it as a control structure.

Another solution (boolen logic only):

WHERE
    v.State_combined != ''
    AND (
        (@component = 'Component 1' AND v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED'))
        OR (@component = 'Component 2' AND v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED'))
        OR (
            @component NOT IN ('Component 1', 'Component 2')
            AND (
                v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')
                OR v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')
            )
        )
    )
    AND (@state IS null OR v.STATE_COMBINED = @state)

Upvotes: 1

Code Maverick
Code Maverick

Reputation: 20415

WHERE  v.State_combined != ''
       AND
       (
           (
               @component = 'Component 1' 
               AND 
               v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')
           ) 
           OR
           (
               @component = 'Component 2' 
               AND 
               v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')
           )
           OR
           (
               v.[Component_1_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED') 
               OR       
               v.[Component_2_Status] IN ('SUBMITTED', 'INCOMPLETE', 'PURCHASED')
           )
       )
       AND 
       (
           @state IS NULL 
           OR 
           v.STATE_COMBINED = @state
       )

Upvotes: 0

Related Questions