2

Is it possible to have conditional where clause, based on declared variable?

Please note: my query is much more complicated than this, I am just using this example to simplify things.

Something like:

DECLARE @ITEST INT = 1

SELECT NAME, LNAME, CADDRESS
FROM JEEVEN
WHERE
CASE WHEN @ITEST = 1 THEN
(
    (EVEN_KEY > 5 AND EVEN_KEY < 10)
)
CASE WHEN @TEST = 2 THEN
(
    (EVEN_KEY > 20 AND EVEN_KEY < 30)
)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FrenkyB
  • 6,625
  • 14
  • 67
  • 114
  • Possible duplicate of [Conditional WHERE clause in SQL Server](https://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) – Tanner Jan 13 '18 at 10:10
  • Possible duplicate of [T-SQL Conditional WHERE Clause](https://stackoverflow.com/questions/4485965/t-sql-conditional-where-clause) – Kevin Hogg Jan 13 '18 at 10:13
  • [This](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer applies to `where` clauses as well as joins. More reading is [here](http://www.sommarskog.se/dyn-search-2008.html), including performance issues. – HABO Jan 13 '18 at 21:11

1 Answers1

3

You are looking for this 

DECLARE @ITEST INT = 1

SELECT NAME, LNAME, CADDRESS
FROM JEEVEN
WHERE (@ITEST = 1 AND EVEN_KEY > 5 AND EVEN_KEY < 10)
   OR (@ITEST = 2 AND EVEN_KEY > 20 AND EVEN_KEY < 30)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172