Greater than in SQL CASE statement

Question

I'm having a hard time figuring out a greater than in a SQL statement.

Here's my code:

select one, two three from orders
where case when @orderid > 0 then orders.orderid = @orderid end

---

@orderid is parameter passed to the stored procedure. The idea is that if a valid (> 0) orderid is passed, then use that as the filter in the where clause, otherwise don't use it all.

Answer 1

Guffa has the right answer, but the way you'd do this using the CASE trick (which does occasionally come in handy) is this:

--If order ID is greater than 0, use it for selection
--otherwise return all of the orders.
select one, two, three
from orders
where orders.orderid = CASE
    WHEN @orderid > 0 then @orderid
    ELSE orders.orderid
END

The CASE always has to return something, so if you want to "disable" a statement in your WHERE clause conditionally and can't use OR, you can just set the thing equal to itself and that should always be true (except when comparing nulls).

Edit: I should also say that on queries like this where the number of rows that can be returned could vary tremendously (one row versus the whole table), using the OPTION (RECOMPILE) hint may help performance a great deal in the single row case.

Answer 2

NYCDotnet answer, like the rest of the answers here works, but they may not be SARGable

To make this non-SARGable query..

select one, two three from orders
where 
     case 
     when @orderid > 0 then orders.orderid  
     else 0
     end = @orderid

..SARGable, do this instead:

select one, two three from orders
where 
     @orderid > 0 and orders.orderid = @orderid

     or not (@orderid > 0)

If @orderid will not ever become negative, just make the solution simpler:

select one, two three from orders
where 
     @orderid > 0 and orders.orderid = @orderid

     or @orderid = 0

Or better yet, i.e. if @orderid will not become negative:

select one, two three from orders
where 
     @orderid = 0

     or orders.orderid = @orderid