Wrong inequality

Question

I am currently using

If @City > 0 then
    SELECT * FROM table1 Where Column1 < @City
ELSE
    SELECT * FROM table1

How can I achieve the same effect in one request?

+3

Alice 09 Feb 13 at 12:24

3 answers

Try the following:

select *
  from table1
 where column1 < @city
    or @city <= 0

0

Hamlet hakobyan 09 Feb 13 at 12:32

Another option

SELECT * 
FROM table1 
WHERE Column1 < CASE WHEN @City > 0 
                     THEN @City 
                     ELSE Column1 + 1 END

0

Aleksandr Fedorenko 10 Feb 13 at 12:35 am

Ben · Accepted Answer · 2013-02-09T12:28:02+0000

You just put a condition in your query:

select *
  from table1
 where ( @city > 0
         and column1 < @city )
    or @city <= 0

Be careful; this can confuse the optimizer a bit and make your first query less efficient. Like all tests before implementation.