Search code examples
sql-serverbooleanquery

Boolean conditions in SQL where clause

I wanted to write an sql query to fetch data as:

1. when param = 'all' it should list data across the table
2. when param = 'yes' it should list data where invoicenumber is not empty.
3. when param = 'no' it should list data where invoicenumber is empty.

i tried below query for yes and no

declare @invoiced as nvarchar(10) = 'no'
select * from OrderSummary 
    where 
((@invoiced = 'yes') or (InvoiceNumber = ''))
    and
((@invoiced = 'no') or (InvoiceNumber <> ''))

now i also want to incorporate all condition, could anyone suggest how could i achieve that

Solution 
  • declare @invoiced as nvarchar(10) = 'no'
select * from OrderSummary 
    where 
    @invoiced = 'all'
    OR 
    (@invoiced = 'yes' AND InvoiceNumber <> '')
    OR 
    (@invoiced = 'no' AND InvoiceNumber = '')