28

I need to have a conditional where clause that operates as so: 

Select *
From Table
If (@booleanResult)
Begin
  Where Column1 = 'value1'
End
Else
Begin
  Where column1 = 'value1' and column2 = 'value2'
End

Any help would be appreciated.

Yatrix
  • 13,361
  • 16
  • 48
  • 78

5 Answers5

46

Could you just do the following?

SELECT
    *
FROM
    Table
WHERE
    (@booleanResult = 1
    AND Column1 = 'value1')
OR
    (@booleanResult = 0
    AND Column1 = 'value1'
    AND Column2 = 'value2')
sazh
  • 1,792
  • 15
  • 20
  • 1
    Won't this give me both sides of the OR in my result? – Yatrix May 09 '12 at 18:22
  • I came back to this and changed the accepted answer. After looking back over it, what I wanted wasn't what I had really conveyed in the question. Siva answered more what I wanted, but you accurately answered what was asked. Sorry about that - this is the fair way to go about it. – Yatrix Jun 05 '12 at 18:31
  • Keep in mind that short circuiting doesn't always work as you expect in SQL. It's left to the db vendor to decide how to evaluate an expression. – Sean Glover Aug 16 '13 at 12:26
  • Just a thank you @diaho as this helped me just now to have a conditional where allowing either evaluation of an ID = 1 OR ID in (1,2,3,4) depending if the user passed anything into the stored proc :). – Richard Griffiths Jun 03 '16 at 10:23
16

You can group conditions easily in a WHERE clause:

WHERE
   (@BooleanResult=1 AND Column1 = 'value1')
OR
   (@BooleanResult=0 AND Column1 = 'value1' AND column2 = 'value2')
JNK
  • 63,321
  • 15
  • 122
  • 138
  • Won't this give me both sides of the OR in my result? – Yatrix May 09 '12 at 18:24
  • 3
    @Yatrix yes it will. But you'll never match BOTH since you are checking `@Booleanresult` and it can't be both `1` and `0`. – JNK May 09 '12 at 18:25
6

Based on the script in question, it seems that you need the condition for Column1 irrespective of whether the variable @booleanResult is set to true or false. So, I have added that condition to the WHERE clause and in the remaining condition checks whether the variable is set to 1 (true) or if it is set to 0 (false) then it will also check for the condition on Column2.

This is just one more way of achieving this.

Create and insert script:

CREATE TABLE MyTable
(
    Column1 VARCHAR(20) NOT NULL
  , Column2 VARCHAR(20) NOT NULL
);

INSERT INTO MyTable (Column1, Column2) VALUES
  ('value1', ''),
  ('',       'value2'),
  ('value1', 'value2');

Script when bit variable is set to 1 (true):

DECLARE @booleanResult BIT
SET @booleanResult = 1

SELECT      *
FROM        MyTable
WHERE       Column1 = 'value1'
AND         (        @booleanResult = 1
                OR  (@booleanResult = 0 AND Column2 = 'value2')
            );

Output:

COLUMN1 COLUMN2
------- -------
value1  
value1  value2

Script when bit variable is set to 0 (false):

DECLARE @booleanResult BIT
SET @booleanResult = 0

SELECT      *
FROM        MyTable
WHERE       Column1 = 'value1'
AND         (        @booleanResult = 1
                OR  (@booleanResult = 0 AND Column2 = 'value2')
            );

Output:

COLUMN1 COLUMN2
------- -------
value1  value2

Demo:

Click here to view the demo in SQL Fiddle.

  • I just thought yours was the most complete example and most helpful to me, personally. Up votes for all, but there can be only one (accepted)! – Yatrix May 09 '12 at 19:04
  • I changed my accepted answer. What you gave me was exactly what I was looking for, but to be fair, as you stated, what they gave me was what I asked for. I just didn't ask the best question. At this point, I can't exactly remember what the question I really wanted was. =) – Yatrix Jun 05 '12 at 18:37
2

To provide a shorter answer:

Select *
From Table
Where Column1 = 'value1' and
      coalesce(column2, '') = (case when @BooleanResults = 0 
                                    then 'value1' 
                                    else coalesce( column2, '')
                               end)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Are you just making up syntax? What RDBMS is that valid for? – JNK May 09 '12 at 18:19
  • Oops, I had left in some extraneous characters. The rest of the syntax is standard SQL and should be understood by any databse that accepts local variables starting with a "@". – Gordon Linoff May 09 '12 at 18:23
  • The answer is shorter, but the code is not. However, +1 for avoiding the _short circuiting_, which makes it useful for more SQL flavors. – GlennFromIowa Dec 09 '15 at 19:07
0

Query 1: if the @CompanyID is set to -1 then all records are selected

Query 2: if the @CompanyID is set to 10 or 11 or 12, only those records are selected where the companyid=@CompanyID

Declare @CompanyID int
Set @CompanyID = -1

select * from sales 
where  1=IIF(@CompanyID =-1, 1, IIF(CompanyID =@CompanyID,1,0))

Set @CompanyID = 10
select * from sales 
where  1=IIF(@CompanyID =-1, 1, IIF(CompanyID =@CompanyID,1,0))
Elikill58
  • 4,050
  • 24
  • 23
  • 45
Pavan Muppalla
  • 9
  • 1