CODEWITHSUNDEEP
sqloracle-database
user1017936
user1017936

Reputation: 153

Best way to handle optional parameter with null value in query

What is the best way to handle optional parameter if null value is passed in select query in oracle

E.g. select * from emp where deptno = 10 and empno in (7782,7934)

In above query, if user will not select any empno then it should fetch all records where deptno = 10

Upvotes: 1

Views: 1422

Answers (2)

nonsleepr
nonsleepr

Reputation: 811

Here is a little bit dirty solution for your problem if you just want to instert the value into the string:

SELECT *
FROM emp
WHERE
    deptno = 10
AND (empno IN (%user_input%,%not_used_value%)
OR   COALESCE(%user_input%,NULL) IS NULL);

Here %user_input% is a placeholder for values, selected by user and %not_used_value% is empno id, which is never used.

So, the resulting query could look like this:

SELECT *
FROM emp
WHERE
    deptno = 10
AND (empno IN (7782,7934,-999)
OR   COALESCE(7782,7934,NULL) IS NULL);

Upvotes: 0

Jake Feasel
Jake Feasel

Reputation: 16955

Usually I handle this case in the programming environment that is issuing the query. Within the code, I check for the null case, and then exclude that part of the where clause as needed. Is that something you can do?

edit

If you were using ColdFusion:

<cfquery>
select 
  * 
from 
  emp 
where 
  deptno = 10
  <cfif Len(empno)> 
     and empno in (<cfqueryparam value="#empno#" list="true" cfsqltype="cf_sql_integer">)       
  </cfif>
</cfquery>

(query params added for correctness)

Upvotes: 1

Related Questions