SQL Server How to add a where clause only if a certain variable is true?

js81xvg6 于 2023-04-04 发布在其他

关注(0)|答案(1)|浏览(188)

I'm trying to select and add a where clause only if a certain variable is true. For example:

SELECT * 
FROM STUDENTS 
WHERE STUDENT_NAME IN ('John', 'Jane');

And my variable is $P{isJohnOrJane} which can be 0 or 1;

I've tried

SELECT * 
FROM STUDENTS 
WHERE $P{isJohnOrJane} = 1 
  AND STUDENT_NAME IN ('John', 'Jane')

and that works. But when ${isJohnOrJane} = 0 it does not select anything when it should select all the students.

sql-server

来源：https://stackoverflow.com/questions/75884165/how-to-add-a-where-clause-only-if-a-certain-variable-is-true

1条答案

按热度按时间

wljmcqd81#

I'm going to use proper SQL declared variables. There are 2 ways to do this: use the variable directly in the query; or use it to determine the query to execute.

First example using the variable directly in the SQL query:

DECLARE @isJohnOrJane bit = 1;

SELECT * 
FROM STUDENTS 
WHERE ( @isJohnOrJane = 1 AND STUDENT_NAME IN ('John', 'Jane') )
     OR @isJohnOrJane = 0

Alternatively you can use IF logic to determine the execution:

IF @isJohnOrJane = 1
 BEGIN SELECT * FROM STUDENTS WHERE STUDENT_NAME IN ('John', 'Jane') ) END
ELSE
 BEGIN SELECT * FROM STUDENTS END

Both will produce the same answer but IF logic works better to avoid weird looking queries containing variables, it uses indexes on table columns as intended, it is probably easier for others to understand, and can apply to multiple outcomes instead of just 2 like in your example.

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server How to add a where clause only if a certain variable is true?

1条答案

相关问题

热门标签

最新问答