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
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 6, 2021 at 11:55 am
Thank you Phil.
but why the addition of "@myFlag <> 1)" make it work ?
(@myFlag <> 1)
OR
(
@myFlag = 1
AND name NOT IN ( 'A', 'C' )
);
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' )
);
May 6, 2021 at 12:04 pm
but why the addition of "@myFlag <> 1)" make it work ?
It's straight logic.
You had two scenarios:
I handled each of those scenarios with the OR in the WHERE clause.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 6, 2021 at 3:11 pm
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".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply