Choose to use '>=' or '>' LOGIC in WHERE Clause Using CASE Statement

  • My WHERE clause is conditional on a parameter such that I need to do as follows:

    --If the parameter @tiDepth > 0 then the WHERE Clause is:

    WHERE @MDepth > @tiDepth

    -- If the parameter @tiDepth = 0 then the WHERE Clause should be:

    WHERE @MDepth >= @tiDepth

    Ideally I was hoping to do this:

    WHERE @MDepth

    CASE @tiDepth

    WHEN 0 THEN >=

    ELSE   >

    END;

    @tiDepth

    BUT I GET AN ERROR IN T-SQL

    The question is how to use CASE structure in T-SQL to choose a conditional LOGIC  operator in an SQL  statement WHERE clause.

     

     

     

     

     

     

  • CASE is an expression not a statement.  That means that the result of WHEN or THEN must be a single value.  No operators (>,>=, etc.) and no keywords can be in the WHEN or THEN expression.

    Here you don't really need a case, you can just check the specific conditions in the WHERE clause:

    WHERE ((@tiDepth = 0 AND @MDepth >= 0) OR (@tiDepth > 0 AND @MDepth > @tiDepth))

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Instead of using a CASE expression (as you were trying) - or stacked OR's:

    WHERE @mDepth > iif(@tDepth = 0, -1, @tDepth)

    Consider: @mDepth >= 0 is equivalent to @mDepth > -1

    Of course, I am not sure why you would be checking 2 variables in the WHERE clause anyways.  If you really are checking 2 variables - that should be done outside the query.  Or - just reset @tDepth before you run the query:

    SET @tDepth = iif(@tDepth = 0, -1, @tDepth);

    Then:

    IF @mDepth > @tDepth
    BEGIN
    {your query here - without the WHERE}
    END

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Instead of using a CASE expression (as you were trying) - or stacked OR's: WHERE @mDepth > iif(@tDepth = 0, -1, @tDepth)

    Consider: @mDepth >= 0 is equivalent to @mDepth > -1

    Of course, I am not sure why you would be checking 2 variables in the WHERE clause anyways.  If you really are checking 2 variables - that should be done outside the query.  Or - just reset @tDepth before you run the query: SET @tDepth = iif(@tDepth = 0, -1, @tDepth);

    Then:

    IF @mDepth > @tDepth
    BEGIN
    {your query here - without the WHERE}
    END

    I hate those types of unnecessary tricks, "tricks for the sake of tricks".  They're very hard for someone else to follow later, like say 4 months from now.  There's no real gain from that vs just doing the straightforward comparisons needed.

    I mean, c'mon, it was confusing enough that you felt the need to comment on it in yourself.  Why add confusion for no real reason?

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    Jeffrey Williams wrote:

    Instead of using a CASE expression (as you were trying) - or stacked OR's: WHERE @mDepth > iif(@tDepth = 0, -1, @tDepth)

    Consider: @mDepth >= 0 is equivalent to @mDepth > -1

    Of course, I am not sure why you would be checking 2 variables in the WHERE clause anyways.  If you really are checking 2 variables - that should be done outside the query.  Or - just reset @tDepth before you run the query: SET @tDepth = iif(@tDepth = 0, -1, @tDepth);

    Then:

    IF @mDepth > @tDepth
    BEGIN
    {your query here - without the WHERE}
    END

    I hate those types of unnecessary tricks, "tricks for the sake of tricks".  They're very hard for someone else to follow later, like say 4 months from now.  There's no real gain from that vs just doing the straightforward comparisons needed.

    I mean, c'mon, it was confusing enough that you felt the need to comment on it in yourself.  Why add confusion for no real reason?

    Instead of going vertical, why not ask if there's a reason?  I'll get the popcorn. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply