CODEWITHSUNDEEP
stored-proceduresdb2
JBaba
JBaba

Reputation: 610

Call one stored procedure to another stored procedure in DB2

Problem that I was facing is that I have to call one stored procedure from another stored procedure in DB2 database. Kind example that I am giving right below.

I have one stored procedure:

CREATE OR REPLACE PROCEDURE Proc1()
 IS    
  Declare myName in varchar;
 BEGIN 
  Select fname into myName from student where fname='x'; // is returning unique value
  -- here call anoher proc2
 END;

Now so this proc1 procedure is going to call this proc2 procedure.

Now I have second stored procedure:

 CREATE OR REPLACE PROCEDURE Proc2(Name in varchar)
 IS
 BEGIN
  -- do anything
 END;

Upvotes: 0

Views: 7589

Answers (1)

JBaba
JBaba

Reputation: 610

I solved this problem, So solution is like If we want to execute proc using sql command then syntex is like below,

call Proc2('My Name');

We can use this same approach inside our proc also. For that we have to follow some steps. Lets say that our above sql call is statement that we want to execute. we are going to convert that statement into String and pass necessary parameter by concating variable values. Then execute statement. 

CREATE OR REPLACE PROCEDURE Proc1()
 IS
  Declare myName in varchar;
  -- stmt variable is to execute our proc
  STMT VARCHAR(4000);
 BEGIN 
  Select fname into myName from student where fname='x'; // is returning unique value
  -- this is our logic
  STMT :='call Proc2('||myName||')';
  EXECUTE IMMEDIATE STMT;
 END;

Upvotes: 2

Related Questions