1

I am looking for the way to execute MySQL statement checking if given parameter exists. As I remember I can do the following in Oracle to achieve that: 

select s.* from Site s 
where s.study = :study
and (:enabled is null or s.enabled = :enabled)

is anything like that possible in MySQL too? The same code executes without error but never return any records.

My goal here is to avoid multiple lfs and elses in my java code. It should work the way that the query looks like that when enabled parameter is null:

select s.* from Site s 
where s.study = :study

and like that if the parameter is not null:

select s.* from Site s 
where s.study = :study
and s.enabled = :enabled

and I want to do that with a single query

smoczyna
  • 489
  • 6
  • 18
  • 1
    `AND (s.enabled IS NULL OR s.enabled = :enabled)` – Francesco Casula Jul 25 '16 at 15:13
  • `:enabled is null` is rather pointless - you can trivially check that in client-side code without using the db to do that, since the `:enabled` value is one YOU supply. you want to check whether the field in the is null, which means you need to test `s.enabled`. – Marc B Jul 25 '16 at 15:22
  • It is not so pointless if you imagine that you have just 1 single query for all possible cases. No ifs and elses man. It's quite usefull. The goal is to check paramter, not the column and disable whole line in that query if parameter is null indeed. – smoczyna Jul 26 '16 at 14:50

2 Answers2

0

I believe this is what you are asking:

SELECT s.* from Site s
WHERE s.study = "some_study"
AND (s.enabled IS NULL OR s.enabled = '' OR s.enabled = "enabled");
BrandonM
  • 390
  • 4
  • 12
  • that's not the case, my goal is to check if parameter exists, not the column. it is to avoid multiple ifs and else in my java code. In Oracle I can do this and don't care about the presence of this value. Effectively SQL parser ignore the line if paramtere is null so it doesn't affect the query – smoczyna Jul 26 '16 at 14:53
  • @smoczyna, perhaps [this](http://stackoverflow.com/questions/9855331/check-if-a-parameter-is-null-or-empty-in-a-stored-procedure) is more what you need? – BrandonM Jul 26 '16 at 14:59
0

Unfortunately it is highly dependent on database driver. My initial query works when run in database tools but doesn't have to when it comes to run it by JPA. So I'm to close this question as it doesn't require further answers. I'm sorry lads for wasting your time.

smoczyna
  • 489
  • 6
  • 18