the_enigma7
asked on
SQL query which will ignore null parameters
I need to write a query which will run a check on a number of fields based on parameters passed in at run time.
if a parameter to check against a field is left null, I want the query to skip the check against that particular field, hence only querying on the fields for which parameters have been entered.
e.g.
if I had:
select * from customers
where name = :param1
age = :param2
surname = :param3
and I gave it the parameters:
name= "John"
age= null
surname= "Smith"
it would only search on the name and surname fields in the customers table.
Any ideas?
Thanks.
if a parameter to check against a field is left null, I want the query to skip the check against that particular field, hence only querying on the fields for which parameters have been entered.
e.g.
if I had:
select * from customers
where name = :param1
age = :param2
surname = :param3
and I gave it the parameters:
name= "John"
age= null
surname= "Smith"
it would only search on the name and surname fields in the customers table.
Any ideas?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can try 'CASE' statement for that.
SELECT * FROM CUSTOMERS
WHERE name = CASE WHEN @a IS NULL THEN name
WHEN @a = 'ignore' THEN name
ELSE @a
END
SELECT * FROM CUSTOMERS
WHERE name = CASE WHEN @a IS NULL THEN name
WHEN @a = 'ignore' THEN name
ELSE @a
END
ASKER