CODEWITHSUNDEEP
sqloracle-database
user2750658
user2750658

Reputation: 59

preparing update statement based on input parameters dynamically

I have to prepare update statement dynamically in stored procedure based on 4 input variables.

suppose take test, test1, test2 and test3 are input parameters.

then I have to prepare query like ,please help me to build this:

update emp 
   set empid='1' 
 where test = test 
   and test1 = test1 
   and test2 = test2 
   and test3 = test3

Suppose if the test and test1 values are null and test2 and test3 values are not null then I can prepare update statement like below

update emp 
    set empid='1' 
  where test is null       
    and test1 is null 
    and test2 = test2 
    and test3 = test3

Upvotes: 0

Views: 246

Answers (3)

Nick Krasnov
Nick Krasnov

Reputation: 27251

If there is a change that one or more parameters that you are passing in a procedure are going to be NULL you could write your UPDATE statement as follows. There is really no need of using dynamic SQL. In this example procedure's parameters are prefixed with p_: 

update emp 
   set empid='1' 
 where (test  = p_test  or (test  is null and p_test  is null)) 
   and (test1 = p_test1 or (test1 is null and p_test1 is null))
   and (test2 = p_test2 or (test2 is null and p_test2 is null)) 
   and (test3 = p_test3 or (test3 is null and p_test3 is null))

Upvotes: 2

Nicolas
Nicolas

Reputation: 933

You can't give your variables the same name as your columns, that would be confusing to PL/SQL. So let's name the variables v_test, v_test1, ...

You could write:

update emp 
       set empid='1'
where  (test  = v_test  OR v_test  is null)
and    (test1 = v_test1 OR v_test1 is null)
and ...

Or more simply using NVL:

update emp 
       set empid='1'
where  test  = nvl(v_test, test) 
and    test1 = nvl(v_test1, test1) 
and ...

NVL(A, B) is A unless A is null in which case it is B.

Upvotes: 0

Harshit
Harshit

Reputation: 560

Create a SQL statement based on your input parameters
like 

 'SELECT '||'Colum_nmae from table' || where var1=var2 .....

then use execute immediate to execute this query

Upvotes: 0

Related Questions