Conditional where clause

Question

Post reply

Conditional where clause

thbaig

Hall of Fame

Points: 3003
More actions
May 6, 2021 at 10:26 am
Go to Answer
#3880035

Hi ,
I wrote a stored proc where I need to evaluate a bit flag and if true append "NOT IN" clause. I don't want to use prepared statement.
I tried following but it failed. Can I use Case or anyother statement to make it work ?

table Test (id int, name varchar(10);
table data
ID-Name
1 -A
2 -B
3 -C
declare @myFlag bit;
begin
set @myFlag=1;
select * from test
where 1=1
AND (@myFlag=1 and name not in ('A','C')) -- not in should only work when @myFlag=1
end

Phil Parkin

SSC Guru

Points: 246960

May 6, 2021 at 10:48 am

Answer

#3880036

DROP TABLE IF EXISTS #Test;

CREATE TABLE #Test
(
    id INT
   ,name VARCHAR(10)
);

INSERT #Test
(
    id
   ,name
)
VALUES
(1, 'A')
,(2, 'B')
,(3, 'C');

DECLARE @myFlag BIT = 1;

--SET @myFlag = 0;

SELECT *
FROM #Test
WHERE (@myFlag <> 1)
      OR
      (
          @myFlag = 1
          AND name NOT IN ( 'A', 'C' )
      );

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

thbaig Hall of Fame Points: 3003 More actions · Answer 1

Thank you Phil.

but why the addition of "@myFlag <> 1)" make it work ?

(@myFlag <> 1)

OR

(

@myFlag = 1

AND name NOT IN ( 'A', 'C' )

);

Phil Parkin wrote:

DROP TABLE IF EXISTS #Test;

CREATE TABLE #Test
(
    id INT
   ,name VARCHAR(10)
);

INSERT #Test
(
    id
   ,name
)
VALUES
(1, 'A')
,(2, 'B')
,(3, 'C');

DECLARE @myFlag BIT = 1;

--SET @myFlag = 0;

SELECT *
FROM #Test
WHERE (@myFlag <> 1)
      OR
      (
          @myFlag = 1
          AND name NOT IN ( 'A', 'C' )
      );

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

but why the addition of "@myFlag <> 1)" make it work ?

It's straight logic.

You had two scenarios:

SELECT all rows if myFlag not 1
SELECT some rows if myFlag = 1

I handled each of those scenarios with the OR in the WHERE clause.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

You don't need the extra check on the flag:

WHERE @myFlag <> 1 OR name NOT IN ('A', 'C')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".