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' )
);
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.
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy