How to write a stored procedure in DB2 to perform UPDATE / DELETE operation by passing Dynamic values?

Question

I want to write a stored procedure in DB2 database for UPDATE and DELETE operations.

I am able to do that by directly providing values in procedure, but I want to do it by passing dynamic values.

My Table Structure is -

create table emp2 (int_1 int, char_1 char(10))

Below is my stored procedure for UPDATE operation which I am able to run but, its not behaving as per expectations. Changes are not reflecting in DB even after passing correct parameters while calling stored procedure.

CREATE OR REPLACE PROCEDURE "DB2INST1"."UPDATE_1" (IN int_1 int, IN 
char_1 char(10)) SPECIFIC UPDATE_1
LANGUAGE SQL
DYNAMIC RESULT SETS 1

BEGIN

update emp2 set char_1=char_1 where int_1=int_1;

END; 

This is the my stored procedure for DELETE operation which I am able to run successfully, but it's deleting all rows from the database table instead of deleting a single row:

CREATE OR REPLACE PROCEDURE "DB2INST1"."DELETE_1" (IN int_1 int) 
SPECIFIC DELETE_1
LANGUAGE SQL
DYNAMIC RESULT SETS 1

BEGIN

delete from emp2 where int_1=int_1;

END; 

Please provide me syntax for creating stored procedure for UPDATE and DELETE operations by passing dynamic values in a DB2 database.

Answer 1

The problem is that you don't qualify your columns and parameters having the same names.
According to the References to SQL parameters, SQL variables, and global variables:

Names that are the same should be explicitly qualified. Qualifying a name clearly indicates whether the name refers to a column, SQL variable, SQL parameter, row variable field, or global variable. If the name is not qualified, or qualified but still ambiguous, the following rules describe whether the name refers to a column, an SQL variable, an SQL parameter, or a global variable:

If the tables and views specified in an SQL routine body exist at the time the routine is created, the name is first checked as a column name. If not found as a column, it is then checked as an SQL variable in the compound statement, then checked as an SQL parameter, and then, finally, checked as a global variable.

That is, the following statements changing all the table rows obviously are equivalent:

update emp2 set char_1 = char_1 where int_1 = int_1;
--and 
update emp2 e set e.char_1 = e.char_1 where e.int_1 = e.int_1;

What you need is to rewrite this statement to the following, if you want to use the same column and parameter names (the routine name UPDATE_1 is used here for the parameter qualification).

update emp2 set char_1 = UPDATE_1.char_1 where int_1 = UPDATE_1.int_1

fiddle