Home / Questions / 317987
Accepted
Alejandro Ricotti
Asked: 2020-12-24 08:44:34 +0800 CST 2020-12-24 08:44:34 +0800 CST

Check dynamic filters pl sql

  • 772

A query

I need to make an sp that receives three string parameters that are used to filter in the where

The doubt I have is that only one can come with data and the other two empty like the three with data

What had occurred to me is to make a string and concatenate depending on whether the variable it receives as a parameter is empty or not.

Is there any other way to do it?

For example if var1 = "test", var2 ="", var3 ="" In the where only var1 should go

On the other hand, if all three have data, all three should go. It may be the case that var2 has data and the other two do not, such as var3 having data and the others not or var2 and var3 having data

Thank you very much

Cheers

sql

1 Answers

  1. Best Answer

    There will be many ways to do this. For now I suggest you make your dynamic query in a variable varchar, and then execute it via execute immediate. (I suggest you investigate more about execute immediateand look for examples)

    create or replace PROCEDURE P1
(
   Var1 IN VARCHAR2,
   Var2 IN VARCHAR2,
   Var3 IN varchar2
)
AS

dynaQuery VARCHAR2(8000);

BEGIN

dynaQuery := 'update table set column1 ='''valor''' where ';

IF var1 <> '' THEN

   dynaQuery := dynaQuery || ' var1 =  '''' || var1 || '''' And '   ;

ENDIF

/* hacer lo mismo con las otras 3 variables*/
/* no olvides borrar el último and */

/*Este comando ejecuta el query dinámico*/
execute immediate dynaQuery;
    • 0