How to Ignore Null Parameters in SQL Query

Question

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.

juergen d · Accepted Answer · 2017-06-14 11:19:17Z

2

Like this

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

answered Jun 14, 2017 at 11:19

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ankit Rauthan Over a year ago

Do i have to write '@' in my query too, i never used @ in sql.Iam using simple sql and using it as imbedded sql with java

juergen d Over a year ago

@value is just a representation for the parameter in your query. Replace it with the search string.

Ankit Rauthan Over a year ago

Thanks man, the only problem was my STUPIDITY.Otherwise the answer was just perfect.I was running your suggested query, and i was getting 0 rows and then i see that i was passing empty string instead of null.That was the problem,otherwise with your solution it should've been breeze.Again, thanks for your help.Try to upvote your solution but doesn't have enough reputation.Again,thanks a lot buddy.Happy Coding..

Collectives™ on Stack Overflow

How to Ignore Null Parameters in SQL Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest