CODEWITHSUNDEEP
windowsstored-proceduresjdbcdb2execute
Wuahaha
Wuahaha

Reputation: 93

How to create or execute a Stored Procedure within another Stored Procedure in IBM DB2?

How to create a Dynamic Stored Procedure using a Stored Procedure in IBM DB2 9.7 for Windows ?

Example below is my dynamic generated stored procedure:

CREATE PROCEDURE DB2ADMIN.INSERT_A (inputVar1 Integer, inputVar2 Integer) 
LANGUAGE SQL
BEGIN
INSERT INTO DB2ADMIN.TABLE_A (var1, var2) VALUES (inputVar1, inputVar2);-- 
END;

Then I have the execute statement stored procedure:

CREATE PROCEDURE ExecuteScript
(
    inputStmt VARCHAR(4000)
)
LANGUAGE SQL
BEGIN   
    DECLARE stmt VARCHAR(4000);--
    SET stmt = inputStmt;--

    PREPARE rs_stmt FROM stmt; --
    EXECUTE rs_stmt;--
END;

Finally I call the stored procedure like this:

CALL ExecuteScript('CREATE PROCEDURE DB2ADMIN.INSERT_A (inputVar1 Integer, inputVar2 Integer) 
LANGUAGE SQL
BEGIN
INSERT INTO DB2ADMIN.TABLE_A (var1, var2) VALUES (inputVar1, inputVar2);-- 
END;')

I got the following error when I call it in DB2 Control Center and also JAVA JDBC:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=LANGUAGE;LANGUAGE SQL;IS, AS, DRIVER=4.14.122

Anyway is it possible to create a stored procedure within a stored procedure? Or just create it using JDBC for IBM DB2? Thanks.

Upvotes: 0

Views: 4506

Answers (2)

Wuahaha
Wuahaha

Reputation: 93

I found the solution after reading through this: http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frzaha%2Fcallexample3.htm

I need to call the stored proc this way by adding the EX3 (can be anything I suppose):

CALL ExecuteScript('CREATE PROCEDURE DB2ADMIN.INSERT_A (inputVar1 Integer, inputVar2 Integer) 
LANGUAGE SQL
EX3: BEGIN
INSERT INTO DB2ADMIN.TABLE_A (var1, var2) VALUES (inputVar1, inputVar2); 
END EX3')

But I'm not sure why.

Upvotes: 0

mustaccio
mustaccio

Reputation: 18945

The CREATE PROCEDURE statement should not have the semicolon at the end of it.

Upvotes: 1

Related Questions