I am able to execute the stored proc successfully db2 if I write normally. Normally in the sense that if I give select statement directly after
DECLARE DB2_SP_SQL1 CURSOR WITH RETURN FOR
But here my requirement is;
I want to assign my stored proc to a variable and then I want to do concatenation for that string according the requirement and later convert this entire string to prepared statement and then execute.
Thats what I tried to do in attached code.
But its not succeeded. I am not getting any data.
I tried the same attached code just by directly giving the select statement. Its worked properly.
Any help is greatly appreciated guys...
Thanks in advance....
CREATE PROCEDURE STOREDPROC1(IN "DEPTID" CHAR(5))
DYNAMIC RESULT SETS 1
-- Declare variables
DECLARE Q1 VARCHAR(1000);
DECLARE Q2 VARCHAR(1000);
DECLARE DB2_SP_SQL1 CURSOR WITH RETURN FOR Q2;
SET Q1 = 'SELECT DEPTNAME, DEPTNUM, EMPLCOUNT FROM DEPT WHERE DEPTID = "DEPTID"';
PREPARE Q2 FROM Q1;
-- Cursor left open for client application.
-- Grant access privileges to procedure
GRANT EXECUTE ON SPECIFIC PROCEDURE STOREDPROC1 TO PUBLIC