CODEWITHSUNDEEP
sql-serverbooleanquery
Abbas
Abbas

Reputation: 5044

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

Upvotes: 0

Views: 151

Answers (4)

Hitesh Thakor
Hitesh Thakor

Reputation: 471

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

Please update this query with above query.

Upvotes: 0

Amit Kumar
Amit Kumar

Reputation: 5962

It should fulfill your requirement.

declare @invoiced as nvarchar(10) = 'no'

select * from OrderSummary 
    where
((@invoiced in ('all','no')) OR (@invoiced = 'yes' AND InvoiceNumber  <> ''))
    and
((@invoiced in ('all','yes')) OR (@invoiced = 'no' AND InvoiceNumber  = ''))
    and
(@invoiced in ('no','yes'))

Upvotes: 0

Felix
Felix

Reputation: 36

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

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try this

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

Upvotes: 0

Related Questions