1

I have a mySql query in which i want to select the records on certain values which i am providing in where clause, these values i am getting dynamically from spring mvc form, how to make sure that if any one of the value is null then ignore that condition check in where clause and check for other not null values.

Here is my query

SELECT qrule.id ruleId    
      ,com.vc_company_name company
      ,cdef.vc_className className
      ,qrule.vc_ruleName rule 
      ,qrule.dt_modifiedOn modifiedOn
      ,qrule.in_modifiedBy modifiedBy
FROM ne_company com qualification_rule qrule, classdefintion cdef
WHERE com.company_id = 42 
AND com.in_status = 13
AND cdef.id = 7 
AND qrule.vc_ruleName LIKE 'h%'

all values in where clause is dynamically generated, what i want is that when company_id is null then com.company_id = 42 this check should be ignored and query should perform like

SELECT qrule.id ruleId    
      ,com.vc_company_name company
      ,cdef.vc_className className
      ,qrule.vc_ruleName rule 
      ,qrule.dt_modifiedOn modifiedOn
      ,qrule.in_modifiedBy modifiedBy
FROM ne_company com qualification_rule qrule, classdefintion cdef
WHERE com.in_status = 13
AND cdef.id = 7 
AND qrule.vc_ruleName LIKE 'h%'

and the same goes for every column.

NOTE: One value is mandatory so we have provided that check,

Search Page

Diwakar Bhatt
  • 495
  • 2
  • 11
  • 24
  • No join conditions? Tip of today: Switch to modern, explicit `JOIN` syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. – jarlh Oct 10 '16 at 09:53
  • @jarlh sure i can do that but to achieve the results here as per the conditions how can i manipulate the query .? – Diwakar Bhatt Oct 10 '16 at 09:56
  • I think this is a duplicate, see here: http://stackoverflow.com/questions/4224991/sql-server-checking-an-input-param-if-not-null-and-using-it-in-where – Jack Wire Oct 10 '16 at 09:58
  • Could you show how, when and where this query is being called? I did this once in vb.net creating my query dynamically according to the selected values in the form. – AsheraH Oct 10 '16 at 09:58
  • It should be done using business code not a sql. – Sergey Morozov Oct 10 '16 at 14:06

0 Answers0