How do I create a conditional WHERE clause?
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.
You can group conditions easily in a WHERE
clause:
WHERE
(@BooleanResult=1 AND Column1 = 'value1')
OR
(@BooleanResult=0 AND Column1 = 'value1' AND column2 = 'value2')
Could you just do the following?
SELECT
*
FROM
Table
WHERE
(@booleanResult = 1
AND Column1 = 'value1')
OR
(@booleanResult = 0
AND Column1 = 'value1'
AND Column2 = 'value2')