1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] Several conditional statements in WHERE clause and Short-Circuit OR

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 21, 2021.

  1. Stack

    Stack Membro Participativo

    I need to write SQL query that has several conditional statements in the WHERE clause. Pseudo code looks something like this:

    SELECT * FROM SomeTable
    //Either one or both of below statements can evaluate to TRUE,
    //to evaluate the the entire WHERE predicate to be TRUE.
    //However, both Ifs need to be checked and not skipped.
    If(@ProductID <> 1) THEN (T1 AND T2)
    If(@ProductType = 2) THEN (T3 AND T4)

    The problem for me is the WHERE statement. WHERE 1 = (CASE....THEN 1) ELSE 0 doesn't work, because as soon as the first If statements is executed, it end the CASE, but I need to execute both If statements. The other way,WHERE ((@ProductID <> 1) AND (T1 AND T2)) OR ((@ProductType = 2) AND (T3 AND T4)) will not work either, because SQL Server optimizer can short circuit the OR and skip the second If statement.

    EDIT: To clear some confusion. T1-4 are predicates. I want to evaluate the entire predicate in WHERE clause to be TRUE, whenever one or all If statements (and it's predicate in THEN) are evaluated to TRUE. But, at the same time, I want all Ifs to be checked. Just like in sequential code, when sequence of If statements are evaluated.

    Continue reading...

Compartilhe esta Página