CODEWITHSUNDEEP
mysql
Ankit Rauthan
Ankit Rauthan

Reputation: 80

How to Ignore Null Parameters in SQL Query

Well I have a problem, and I don't even know that what I'm trying or thinking to do is possible or not.

I have a sql query:-

 select * 
 from table
 where (col1 >= '2016-07-05' and col2 <= '2016-07-07')
 and col3 = '' 
 and col4 = ''
 and col5 = ''
 and col6 = '686486'
 and col7 = ''
 and col8 = '';

Ok I'm using this query to perform a search operation, i want it to be very specific that's why i'm thinking of doing it this way.

All these parameters can be null or can have any value, So all i want is to ignore those paramters whose value is null.

For Eg:- For above query the result should be same as

 select * 
 from vehicle
 where (col1 >= '2016-07-05' and col2 <= '2016-07-07')
 and col6 = '686486';

Edit 1: First of all thanks for helping me out,I tried what was suggested

 select * 
 from table
 where (('val1' is null or col1 >= 'val1') and ('val2' is null or col2 <= 
  'val2'))
 and ('val3' is null or col3 = 'val3')
 and ('val4' is null or col4 = 'val4')
 and ('val5' is null or col5 = 'val5')
 and ('val6' is null or col6 = 'val6')
 and ('val7' is null or col7 = 'val7')
 and ('val8' is null or col8 = 'val8');

But I'm getting 0 rows as a result for this query, Am I doing something wrong.

Upvotes: 0

Views: 2496

Answers (1)

juergen d
juergen d

Reputation: 204756

Like this

...
and (@value3 is null or col3 = @value3)
and (@value4 is null or col4 = @value4)
....

Upvotes: 2

Related Questions