--DROP PROCEDURE BXBB72DA.QUERY_TAB RESTRICT;
CREATE PROCEDURE BXBB72DA.QUERY_TAB
(IN TABNAME CHAR(18))
DECLARE V_NAME CHAR(18);
SELECT * FROM SYSIBM.SYSTABLES WHERE NAME = TABNAME;
SELECT BXBB72DA.QUERY_TAB('P%') FROM SYSIBM.SYSDUMMY1;
FUNCTION BXBB72DA.QUERY_TAB was not found.
Are you using Stored Procedure Builder to build the SQL stored procedure? You can also test the stored procedure from Stored Procedure Builder. It seems that the definition of a SQL stored procedure you have is not complete. Stored procedure builder will compile it and create an associated package in DB2. Also, the stored procedure does not return a value. You can use the CALL command to execute the stored procedure from a trigger. I am not sure how the CALL will work in SPUFI or QMF.
I just ran a test query. The stored procedure will not execute using SQL. (Sorry, I was getting it confused with calling a UDF) Speaking of UDF's. You could write a simple program that uses the CALL command to call the stored procedure and create a UDF to execute the program. THEN, you can use the UDF in an sql statement to call the stored procedure. Gee, isn't that simple?
Hi I am new to this forum.
I believe QMF v7.2 does not support calling DB2 Stored Procedures period.
However QMF or Windows v7.2 does.
The SQL statement CREATE PROCEDURE XYZ.... should work from any client since it is SQL that is executed at the Database server (provided there is a suitable compiler if any compiling is called for by the language of the sproc)
The SQL statement CALL XYX(param1,param2...) on the other hand requires the client app potentially to handle multiple result sets, and so CALL will not be supported by every client app that can talk to databases
I believe that QMF v8 introduced support for calling DB2 Stored Procedures with the CALL syntax. It also introduced in the QMF HPO component a DB2 Cobol Stored Procedure builder.