How to handle optional parameters in SQL query?

by

Yes, using any of the following:

WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);

…are not sargable. They will work, but perform the worst of the available options.

If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.

The next option after that is dynamic SQL. But coding dynamic SQL is useless if you carry over the non-sargable predicates in the first example. Dynamic SQL allows you to tailor the query while accommodating numerous paths. But it also risks SQL injection, so it should be performed behind parameterized queries (preferably within stored procedures/functions in packages.

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. PL SQL UPDATE STATEMENT
  3. Splitting string into multiple rows in Oracle
  4. Search All Fields In All Tables For A Specific Value (Oracle)
  5. What is the string concatenation operator in Oracle?
  6. Is it possible to output a SELECT statement from a PL/SQL block?
  7. SQL recursive query on self referencing table (Oracle)
  8. How can you tell if a value is not numeric in Oracle?
  9. IF EXISTS condition not working with PLSQL
  10. PLS-00428: an INTO clause is expected in this SELECT statement
  11. passing table and column name dynamically using bind variables
  12. SQL error “ORA-01722: invalid number”
  13. Is there any way to flush output from PL/SQL in Oracle?
  14. Sleep function in ORACLE
  15. Solution to “cannot perform a DML operation inside a query”?
  16. Array in IN() clause oracle PLSQL
  17. PLSQL Insert into with subquery and returning clause
  18. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  19. What does the colon sign “:” do in a SQL query?
  20. How to query a CLOB column in Oracle
  21. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  22. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  23. Advice Using Pivot Table in Oracle
  24. In Oracle, is it possible to INSERT or UPDATE a record through a view?
  25. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  26. How to use Alias in Where clause? [duplicate]
  27. Subtracting Dates in Oracle – Number or Interval Datatype?
  28. How to call Oracle MD5 hash function?
  29. ORACLE After update trigger: solving ORA-04091 mutating table error
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment